Rounding time according to entry in a cell

Smiling Carcass · Mar 13, 2012

I have developed the following formula-

=IF(F3="","",IF(E3>="*06",ROUND(F3*96,0)/96-1/96,ROUND(F3*96,0)/96))

Which is designed to take a time in from D2, a time out from E3 and return the time difference, rounding down to the nearest ¼ hour if it is six or less minutes past the hour, and rounding up if it is seven minutes or more past the hour; it works perfectly well from 01 to 06 minutes past the hour, rounding down as it should. It works perfectly well from 08 minutes past the hour, rounding up as it should. However, for 07 minutes past the hour it is supposed to round up, but instead rounds down. Can anybody see my mistake?

RonB1111 · Mar 13, 2012

Try:
=IF(F3="","",ROUND((F3+"0:01")*96,0)/96)

barry houdini · Mar 13, 2012

This version should also work for you

=IF(F3="","",FLOOR(F3+"0:08","0:15"))

Smiling Carcass · Mar 14, 2012

Thanks for the help, guys. Both work for the example above, rounding down from 06 and up from 07; unfortunately, I need a nested expression that will also test for 23 mins, 37 mins and 53 mins past the hour and round up or down to the nearest quarter hour. I thought I would be able to get there once I had a working expression for the quarter hour, merely nesting the equation with the alternative values, but I keep getting errors.

Can you explain why my formula doesn’t work? It looks to me like it should and I am very frustrated it won’t do it for 7 mins.

Any ideas?

RonB1111 · Mar 14, 2012

Both formula's already work for rounding down to nearest 15 minutes from :06 :21 :36 :51 and rounding up from :07 :22 :37 :52. Try them with any time to verify.

RonB1111 · Mar 14, 2012

Regarding your formula:

ROUND(F3*96,0)/96-1/96 is subtracting 15 min (-1/96) after its already been rounded up or down to the nearest 15 min by the first part ROUND(F3*96,0)/96.

IF(E3>="*06" I not familiar with using syntax with the * in "*06". You might try "0:06". Also I don't know what you have in cell E3.

Smiling Carcass · Mar 14, 2012

RonB1111 said:
Both formula's already work for rounding down to nearest 15 minutes from :06 :21 :36 :51 and rounding up from :07 :22 :37 :52. Try them with any time to verify.

I have tested them, and they do work as you say, but... and I feel like I am being a real pain here, I need rounding down to the nearest 15 mins from :06, :22, :36, :52 and up from :07, :23, :37, :53; works great for :06, :36 and :07, :37 but not :22, :52 and :23, :53. Only a minute I know, but it puts my timesheet out 15 minutes on those entries.

Smiling Carcass · Mar 14, 2012

RonB1111 said:
Regarding your formula:

ROUND(F3*96,0)/96-1/96 is subtracting 15 min (-1/96) after its already been rounded up or down to the nearest 15 min by the first part ROUND(F3*96,0)/96.

I see what you mean- the ROUND function has already done the work... yes?

IF(E3>="*06" I not familiar with using syntax with the * in "*06". You might try "0:06". Also I don't know what you have in cell E3.

Cell E3 is the out time. “*06” should have read “*:06” the asterisk being ignore any value before the minutes (the hours) and apply the formula purely on the minutes past the hour; using “0:06” would, I think only apply the formula for minutes past midnight.

barry houdini · Mar 14, 2012

Smiling Carcass said:
....I need rounding down to the nearest 15 mins from :06, :22, :36, :52 and up from :07, :23, :37, :53; ....

Try this version

=IF(F3="","",LOOKUP(MINUTE(F3),{0,7,23,37,53;0,15,30,45,60})/1440+HOUR(F3)/24)

Smiling Carcass · Mar 14, 2012

barry houdini said:
Try this version

=IF(F3="","",LOOKUP(MINUTE(F3),{0,7,23,37,53;0,15,30,45,60})/1440+HOUR(F3)/24)

Yes, yes, yes, yes, yes! Thanks so much. My original spreadsheet had three columns, one for exact time worked, one rounding up and one rounding down to give an approximation of hours worked; I have been trying for three weeks to combine the two to round up or down according to the minute value in E3 or F3; not being aware of the LOOKUP(MINUTE) function, I could hardly try it or do a search on it.

And the most frustrating thing is being aware of the power of Excel and that one should be able to do it only increases frenzy!

 I would still like to understand why the IF(E3>="*:06", worked for everything except :07 minutes; having looked long and hard at it, I am assuming that Excel ignores this, and does the ROUND function based on its interpretation of the midway point between 0 and 15 minutes. But why does it ignore it and why no error?

Thanks once again. May your shadow never grow shorter.

Rounding time according to entry in a cell

Smiling Carcass

New Member

Excel Facts

RonB1111

Well-known Member

barry houdini

MrExcel MVP

Smiling Carcass

New Member

RonB1111

Well-known Member

RonB1111

Well-known Member

Smiling Carcass

New Member

Smiling Carcass

New Member

barry houdini

MrExcel MVP

Smiling Carcass

New Member

Similar threads

Forum statistics

Share this page

Rounding time according to entry in a cell

New Member

Excel Facts

Well-known Member

MrExcel MVP

New Member

Well-known Member

Well-known Member

New Member

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock