Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Round times to nearest quarter hour

Highlighted
Atom

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!!

 

 

Alteryx Partner

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).  

 

Round Nearest Qtr Hour.png

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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

Highlighted
Atom

Great! Thank you both for your solutions, I guess there are two ways to look at it. When we had it in Excel we were rounding first to make it easier to calculate in a second step, but you are correct @john_miller9 . 

 

Thank you!

Labels