Time Round Up or Down

Fresno-Son-of-Bear wrote:

automatically round to the nearest 15 minute mark of “12:45AM.” Is the a formula I can do this with?

You can set up a column next to the column where you enter the Now value, and add a formula like this:

="0:00"+MROUND(TIMEVALUE(D2),1/1440*15)

1440 is the number of minutes in 24 hours.

TIMEVALUE returns the fraction of a day since midnight the previous day.

MROUND then rounds up or down to the nearest 15 minutes.

TIMEVALUE - Apple Support

MROUND - Apple Support

SG

Hi Fresno-Son-of-Bear,

1. In Numbers, time does not work as it does in Excel.
2. In Numbers, the NOW function is literally now. It is not a time stamp. NOW updates whenever changes to a table occur.
3. I suggest that using 24 hour time without the date will make this simpler.

Try this:

The table NOW is formatted as Date & Time to display only the time portion.

Click the Tick Box to update NOW and immediately copy.

In table 1:

Column B formatted as Date & Time 12 hour clock.

Click in the next B cell and Menu > Edit > Paste Formula Results. That will "freeze" the time.

Column C formatted as Date & Time 24 hour clock.

Formula in C2: B2

Formula in D2: TEXTBEFORE(C2,":")

Formula in E2: TEXTAFTER(C2,":")

Formula in F2: MROUND(E2,15)

Column G formatted as Date & Time 12 hour clock.

Formula in G2: TIME(D2,F2,0)

Regards,

Ian.

Assuming the NOW button on the iOS version of Numbers is the same as "Insert Current Time" on the Mac OS version, the NOW button does not have a setting to round the time when it inserts it. You can round the times when you are calculating the labor hours for the day but you cannot round the times in the cells where you use the NOW function.

As noted here, after you extract the time from the rest of the date you can use:

=ROUND(time*4,0)/4

For example, if you have a time value of 2:30 PM, which is 2.5 in decimal format, you would use the following formula:

=ROUND(2.5*4,0)/4

This formula will round the time to the nearest quarter hour, which is 2:30 PM.