IF and TIME questions

New Contributor

Hi All,

I am trying to build a spreadsheet for punch clock time rounding to the nearest quarter hour.

If the time is 3 minutes before the quarter hour, I need the time to jump up to the nearest quarter hour, if the time is three minutes after the quarter hour I need it to jump down to the nearest quarter hour.

Example 1: 6:02 punch in equals 6:00

Example 2: 6:28 punch in equals 6:30

Example 3: 7:07 punch in equals 7:15

Can anyone help me with the formula?  I am struggling to get it right.

Thank you.


2 Replies

@LScott2514 Perhaps with MROUND. See below.

Screenshot 2022-09-06 at 18.31.58.png

@Riny_van_Eekelen - Thank you!


That gets me closer, thank you so much.  I just now need to incorporate the 3 minute rule.  This is rounding based on dividing each quarter in half, rather than overwriting that with a three minute threshold.