Rounding finish time with 6-minute offset in Numbers spreadsheet

Question

Level 2

191 points

Rounding finish time with 6-minute offset in Numbers spreadsheet

Hi everyone. I work in a job where my finish time varies from one day to the next. I have a Numbers spreadsheet that I’ve been using to keep track of my hours and generate a payslip that should match what I’m eventually paid.

My finish time is always rounded to the nearest 15 minute mark and I use a formula to give the desired rounded time. Until recently, that has been quite simple as the times were rounded in equal 15 minute intervals (00, 15, 30 & 45). However, a recent overhaul of the business has seen many changes, including our number of rostered hours, which affects the finish times. Our finish times are still rounded to 15 minute intervals, but they are no longer at the usual 15 minute marks – there is a 6 minute offset (06, 21, 36 & 51).

I’m sure there is a simple adjustment that can be made to the formula that will give the desired results, but I’m definitely not as clever as I once was, so I can’t see how to do it without making the formula unnecessarily convoluted.

The attached formula has worked flawlessly for me in the past, but any advice on how to amend it to resolve the issue (or simplify what I do have, even further), would be greatly appreciated.

All values outside the header column are date/time values that only display the time in 24 hour format.

I’ve added a row, that is not part of the formula, which displays my desired results.

Thanks in advance – Trevor.

[Re-Titled by Moderator]

MacBook Pro 15″, macOS 13.7

Posted on Jan 3, 2025 6:28 PM

Reply

Answer 1

SGIII

Level 8

37,550 points

Jan 5, 2025 1:50 AM in response to Lovecross

Lovecross wrote:

I’ve never known that I could add 6 minutes to a formula by simply typing +”6m”.

I don't think that's well documented! I probably first read about it somewhere in these communities. Knowing the shorthand for Duration values is handy, especially when entering values into cells. In the English-speaking word and many other locales, you can use d,h,m,s,ms. Entering 1h 15m is clear. Entering and displaying 1:15 is ambiguous as it could mean a point in time (Date & Time) or it could be a span of time (Duration).

The "correct" way to add 6 minutes that should work in all locales would be to use the DURATION function, like this.

+DURATION(,,,6)

(More on DURATION here: DURATION - Apple Support . Excel stores handles dates and times differently, and has no equivalent function.)

But DURATION can look strange with all those mandatory leading commas. In many situations I find it easier and clearer to just use +"6m".

SG

Reply

Answer 2

Lovecross Author

Level 2

191 points

Jan 3, 2025 6:39 PM in response to Lovecross

Looking at the example formula, I can see why someone would feel that it’s a little crazy, as I’ve added the date and then subtracted it, which would render both steps pointless. My working spreadsheet actually draws the first (added date) from a cell that I have not included in this example, which is the date when I commence my shift. I sometimes finish my shift after midnight, which is why those steps are included. Please disregard the fact that those two steps cancel each other out in the example given.

Reply

Answer 3

Lovecross Author

Level 2

191 points

Jan 4, 2025 6:33 PM in response to SGIII

Thank you so much SG – I can’t begin to express how grateful I am for your efforts. I never would have thought to approach it in this manner.

I had to make a couple of minor adjustments, because the today function may change the date each time I open or interact with the sheet and it needs to remain fixed, but it’s easy to extract the date from the actual time that is entered. Also, while the time format doesn’t display the seconds, they are still present. In the examples in my original post, for the first time entry (column B) I just entered the current time, which was 11:50:44 am, then added a minute for each of the following columns, giving the displayed time plus 44 seconds. When I entered your formula, all resulting values matched what I needed except column J, which resulted in 12:06. Once I worked out that the 44 seconds were pushing the time forward enough to round it up, instead of down, I added a step in the timevalue function to subtract the seconds portion which resolved the issue.

Again, a huge thank you for your time and effort. This isn’t the first time you’ve helped me out of a jam with Apple Numbers and you’ve helped me by more than just solving this problem. I’m mostly self taught, so there are many things I don’t know and, until now, I’ve never known that I could add 6 minutes to a formula by simply typing +”6m”. You’ve forever changed the way I enter fixed time values and opened my eyes enough to consider entering other values in such a manner.

The Apple Support Community is covered in your excellent advice and I really can’t imagine how you find the time to help so many people. I need to know – do you have a method where those you have helped can donate some money? A Patreon account, GoFundMe, or something similar? I really want to buy you some beers (coffee, kombucha, whatever your poison is).

Reply

Answer 4

Lovecross Author

Level 2

191 points

Jan 4, 2025 7:05 PM in response to SGIII

I’ve been searching all over (this post, google, my email regarding your reply) trying to work out how to mark this topic as solved by your answer. It’s been a while since I’ve had to consult the Apple Support Community and either I’ve forgotten how, or it has been changed since I last did it. I don’t recall it being a difficult task, but I can’t find anywhere a button to mark your answer as the best answer or to mark the topic as solved. How do I go about that?

Reply

Answer 5

SGIII

Level 8

37,550 points

Jan 5, 2025 1:56 AM in response to Lovecross

Lovecross wrote:

trying to work out how to mark this topic as solved by your answer. It’s been a while since I’ve had to consult the Apple Support Community and either I’ve forgotten how, or it has been changed since I last did it.

The Support Communities are constantly evolving. Upvotes (and downvotes) have replaced the old "Solved" and "Helpful" system. Keeps us all on our toes!

I appreciate your kind words.

SG

Reply

Answer 6

Lovecross Author

Level 2

191 points

Jan 5, 2025 6:24 PM in response to SGIII

Thanks SG. Appreciate all your help.

Regards – Trevor

Reply

Answer 7

SGIII

Level 8

37,550 points

Jan 4, 2025 3:28 AM in response to Lovecross

Not a simple problem! Makes us all feel a little less clever than we once thought we were.😀

After trying different approaches I came up with a solution that gives what you specify. In the end it turned out to be surprisingly concise:

For rounded time, try this in B4, filling it right:

=TODAY()+MROUND(TIMEVALUE(B2)*60*24−6,15)/60/24+"6m"

Here my step-by-step derivation to verify it is doing what you want:

The idea is to get the TIMEVALUE (the fraction of a day since midnight), convert that to minutes, subtract your offset of 6 minutes, use MROUND to round to the closest 15-minutes, convert the rounded minutes back to a fraction of a day, add that rounded fraction of a day to TODAY (midnight), and finally add back the 6-minute offset (as a duration instead of decimal minutes, since it is being added to a Date & Time value).

For review more on the functions here:

TIMEVALUE - Apple Support

MROUND - Apple Support

SG

Reply

Rounding finish time with 6-minute offset in Numbers spreadsheet

Similar questions