Rounding DateTime values to the nearest 15 minutes

Thursday 24 February 2022 @ 2:49 pm

This morning a customer asked me to round a DateTime value to the nearest quarter hour. I had written this logic for them before but we couldn’t find the formula so I started from scratch. When I was done it didn’t look familiar so I suspected I had used a different approach this time.

Later we found the original formula, and it was longer and more complex. That is when I remembered writing it, and saying at the time that there had to be a more elegant approach. I must have been off that day because I couldn’t seem to find the simple solution and just wrote something that worked.  Today it clicked:

Local DateTimeVar DT := {Orders.Ship Date};
Local DateVar D := Date(DT);
Local numbervar Sec := DateDiff('s', D, DT);
DateAdd('s', Mround(Sec, 15*60), D);

And thanks to Ido Millet of Millet Software for suggesting the even better approach to this formula now shown above.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)







Leave a Reply

Jeff-Net
Recrystallize Pro

Crystal Reports Server