cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Yojo Frequent Visitor
Frequent Visitor

Re: Round time DOWN to the nearest 5/15/30 minutes

To round down to nearest 15 mins, 30 mins, 1 hour, etc.

 

=FLOOR([Time],"0:15")

=FLOOR([Time],"0:30")

=FLOOR([Time],"1:00")

 

To round up, use CEILING(). Both work with any Date/Time value. They obviously work in a more conventional sense such as flooring/ceiling a number to the nearest 10, 100, 50, whatever....

 

=FLOOR(323,100) will be 300

=CEILING(59204,10) will be 59210

pauledmonds Visitor
Visitor

Re: Round time DOWN to the nearest 5/15/30 minutes

This is the formula that works:

 

ColRndTime = MROUND(Query1[ColTime],time(0,15,0))

Where: Data type = Time

And: Format = HH:mm

 

ColTime = Query1[READ_DATE]    

Where: Data type = Time

And: Format = HH:mm

 

READ_DATE = Data and Time of the source data

Where: Data type = Date / Time

And: Format = (G)

Lorian Regular Visitor
Regular Visitor

Re: Round time DOWN to the nearest 5/15/30 minutes

Works brilliantly and the switch statement can be used in direct query calculated columns which is what I needed. Thank you.