Round times to nearest quarter hour

Question

I'm trying to find the difference between start and end times, to the nearest quarter hour, but I'm having a problem with the rounding. There is a great solution for rounding down posted on the community, but I need to go up or down to the nearest 15 minutes. To add another layer, my start and end times are not always on the same day. It is common for the start time to be in the evening and the end time to be early the next morning.

My end goal is to reflect the entire treatment time as the number of hours rounded to the nearest quarter hour.

For example:

Start time 22:28

End time 00:07

Total hours: 1.50

I attached a simple table with some sample data. Any assistance is appreciated!!

round to quarter hour.yxmd

john_miller9 · Accepted Answer

Take a look at the solution attached.  Looks like it gets to what you're looking for, however in the example  you provide I'm showing it should be rounded up (1 hour 39 minutes rounded to 1.75 hours rather than 1.5).

BenMoss · Accepted Answer

I got to the 1.5 hour answer by rounding to 15 minutes prior to building out the different rather than after, however I kindof think that @john_miller9 and his solution is 'correct' in the sense that it should in fact be 1.75, but if you want to do the rounding first then my method is attached; but it's quite messy (so another reason to go for Johns solution!)

Ben