Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Paknanarn23
Creator II
Creator II

How do I sum this condition?

I want to sum AmpH from Date Out starting at 05:00:00 AM of the first day to 05:00:00 AM of the next day.

Paknanarn23_0-1675397660416.png

 

Labels (4)
1 Solution

Accepted Solutions
avkeep01
Partner - Specialist
Partner - Specialist

Hi @Paknanarn23 , 

You could use this to get 1 date for all values between 05:00:00 this day and 05:00:00 the next day. If the date is later then 12:00:00 PM then it will substract 1 day for the timestamps before 05:00:00. 

TIMESTAMP(DATE(IF(TIME(DATE_OUT-DAYSTART(DATE_OUT))>=TIME('05:00:00') ,DAYSTART(DATE_OUT),DAYSTART(DATE_OUT)-1),'DD-MM-YYYY')+TIME('05:00:00'),'M/DD/YYYY h:mm:ss TT') AS GroupByDate

This is the result: 

avkeep01_0-1675415136797.png

 

 

View solution in original post

5 Replies
tresesco
MVP
MVP

Can you explain how you are getting these numbers and how do you want to sum them?

Paknanarn23
Creator II
Creator II
Author

Hi  ,

I got this sum from Database, it's a value fetched from Database at all.

where I want to sum the Amph values ​​from Date Out 5:00 AM of the first selected date to 5:00 AM of the last selected date.

tresesco
MVP
MVP

I am still not clear about your 5 a.m. part. Could you explain with a selection case?

Paknanarn23
Creator II
Creator II
Author

It's not a choice. But it is a condition for sum(AmpH) that if the time is 5am, sum(ampH) will come out in each Cu Tank

For example, if CU TANK = 26, you will get this value.

The time will start from 6am on January 30, 2023 until 4am on January 31, 2023

2023_02_03_11_55_21_Microsoft_Excel_AmpH.png

 

 

avkeep01
Partner - Specialist
Partner - Specialist

Hi @Paknanarn23 , 

You could use this to get 1 date for all values between 05:00:00 this day and 05:00:00 the next day. If the date is later then 12:00:00 PM then it will substract 1 day for the timestamps before 05:00:00. 

TIMESTAMP(DATE(IF(TIME(DATE_OUT-DAYSTART(DATE_OUT))>=TIME('05:00:00') ,DAYSTART(DATE_OUT),DAYSTART(DATE_OUT)-1),'DD-MM-YYYY')+TIME('05:00:00'),'M/DD/YYYY h:mm:ss TT') AS GroupByDate

This is the result: 

avkeep01_0-1675415136797.png