Rounding time to the nearest 15 minutes

[Josh_Klassen]

Okay, so I have tried to use this formula to round to the nearest 15 minute interval and for the most part it works. It just doesn't work the way I want when I am trying to get it to round to the next hour. For example if I input 12:55 it doesn't give an output it's just blank. EDIT: However if I change the time to 12:42 it does round to 12:45. It's only the last rounding that isn't working. The last rounding being towards the next full hour.  I must be missing something.

TIME(
CONCATENATE(
HOUR([Time In] - "00:00:00"),
":",
(
ROUND(
TOTALSECONDS(
([Time In] - "00:00:00")
- HOUR([Time In] - "00:00:00")
)
/ 60.0
/ 15.0
)
* 15
),
":0"
)
)

In these attached images [New Time In] is the result of the formula taking values from [Time In]

[Suvrutt_Gurjar]

You may want to consider the following very useful tips and tricks posts on the topic by @Steve and @GreenFlux 

https://www.googlecloudcommunity.com/gc/Tips-Tricks/FAQ-Rounding-DateTime-Duration-and-Time-values/m...

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Set-Initial-Value-of-TIME-column-to-nearest-15-m...

[Josh_Klassen]

Yes, I have read and re read and read again both these posts. Yet I am still missing something. You will see that I have posted replies on both these posts. Please note I did add further explanation to my question in the original post. 

Thanks again, 

Josh 

[Josh_Klassen]

@Steve Any chance you could help me with this? Sorry to be a bother but I am really wanting to use this formula. It has been an idea I have come back to many times to add to my app but without success. 

Thanks,

Josh 

[Josh_Klassen]

Some more information,

These are the same columns when the formula does work. "new time in" calculates the "time in" column with this formula

It rounds correctly for every time for example :17, :32, :33 , :34 , :35 etc... up until :52 anything after that :53, :54, :55, :56 , :57 , :58 ,:59 does not work.

[Suvrutt_Gurjar · Accepted solution]

Please try the below

IF(

ROUND(
TOTALSECONDS(
([Time In] - "00:00:00")
- HOUR([Time In] - "00:00:00")
)
/ 60.0
/ 15.0
)
<4,

TIME(
CONCATENATE(
HOUR([Time In] - "00:00:00"),
":",
(
ROUND(
TOTALSECONDS(
([Time In] - "00:00:00")
- HOUR([Time In] - "00:00:00")
)
/ 60.0
/ 15.0
)
* 15
),
":0"
)
), TIME(HOUR([Time In]-"00:00:00") + "001:00:00")
)

[Josh_Klassen]

Hey! Thanks! that worked!!!! :):):) I'm not sure why and I have no idea how you came up with that but it worked. Thank you thank you thank you 

[Suvrutt_Gurjar]

Hi @Josh_Klassen , Good to know that it works as desired. However please do perform more tests, especially transition from AM to PM and vice versa etc. Date and Time expressions can be tricky for edge cases.

Well, the modifications to your expression were as follows

1. When trying to round off minutes , the expression divides the minutes by 15 and then rounds off to nearest integer.  So till 52, the expression works because 52/15=3.47 which rounds off to 3. So the expression adds 45 minutes ( or 30 for 2 and 15 for 1)

2. But 53 onwards the division (Minutes /15 ) results into more than 3.5 which rounds off to 4, So the expression tries to add 60 (15*4) .  One cannot add 60 minutes this way. Instead one should add 1 hour to the hour component of the time. 

The revised expression exactly does that 

The part 

IF(

ROUND(
TOTALSECONDS(
([Time In] - "00:00:00")
- HOUR([Time In] - "00:00:00")
)
/ 60.0
/ 15.0
)
<4,

checks if the division is less than 4 , then it adds upto 45 ( or 15, 30)  minutes. It basically uses the expression you had constructed.

If the division is 4,  it adds 1 hour to the time instead of 60 minutes.

TIME(HOUR([Time In]-"00:00:00") + "001:00:00")

Hope this helps. 

[Peter_Bernsen]

Hi @Suvrutt_Gurjar Is there a way to alway round like floor division?

14:01 becomes 14:00
14:14 becomes 14:00.
14:15: stays 14:15 

Regards Peter

[Suvrutt_Gurjar]

---

@Peter_Bernsen wrote:

Is there a way to alway round like floor division?

---

The following is not very elegant, but please try and please test well.

         TIME( CONCATENATE(HOUR([TimeIn]-"00:00"),":",
                                     IFS(MINUTE([TimeIn]-"00:00")=0,"00",
                                            MINUTE([TimeIn]-"00:00")<15,"00",
                                            MINUTE([TimeIn]-"00:00")<30,"15",
                                            MINUTE([TimeIn]-"00:00")<45,"30",
                                            MINUTE([TimeIn]-"00:00")<60,"45"

                                          )
                                        ))

[Peter_Bernsen]

Hi @Suvrutt_Gurjar. It works and tested it.
With the [TimeOut] I want to do the opposit.
How to do the same but then as cieling.  
17:31 becomes  17:45
17:46 becomes 18:00
In this IFS does not go one hour up and 17:46 goes back to 17:00 and should go to 18:00.
How to add one hour up? 
Could this be done with the IFS?

[Suvrutt_Gurjar]

Since I just worked on it, I will post it. However please do take a look at @Steve 's solution as it is much better documented and much more versatile and efficient for calculating different time intervals and not just 15 minutes as the below case is

Below expression will round up as 

---

@Peter_Bernsen wrote:

How to do the same but then as cieling.  
17:31 becomes  17:45
17:46 becomes 18:00

---

TIME(CONCATENATE(IF(MINUTE([InputTime]-"00:00")<46,
HOUR([InputTime]-"00:00"),
HOUR(([InputTime]+1)-"00:00")
),":",
IFS(MINUTE([InputTime]-"00:00")=0,"00",
MINUTE([InputTime]-"00:00")<16,"15",
MINUTE([TimeTest]-"00:00")<31,"30",
MINUTE([TimeTest]-"00:00")<46,"45",
MINUTE([TimeTest]-"00:00")>45,"00"
)
))

[Peter_Bernsen]

I do like this approach because it gives me the possibility to manually set times to what the customer wants.

It works great and ... changed the last IFS >45,"00" to >46,"00"
And changed [TimeTest] to {[nputTime].
Now I see the answer it looks more easy.

Thanks again!! 

[Steve]

FAQ: Rounding DateTime, Duration, and Time values - Google Cloud Community

[Peter_Bernsen]

@Steve : thanks for your link and solution will look into this. 
What I still need to look in the rounding you created to have a cieling or floor round type. Is this to do?