CODEWITHSUNDEEP
datetimegoogle-sheetsgoogle-sheets-formula
Rachel Miller
Rachel Miller

Reputation: 11

How to round up 15 mins for more than 24hrs. and have it show correct number

My formulas are in red next to the corresponding cells (obviously without the "=" sign).

I've tried searching this forum for any assistance, and cannot find anything.
I appreciate any help in advance!!

As soon as the total time goes over 24 hours, it breaks the whole thing.

Example of Working Sheet:

TIME FORMULAS
2:00
10:00
0:35
6:00
TOTAL: 18:35 =TEXT(SUM(B2:B5), "[h]:mm")
ROUND (nearest 15 min) 18:45 =ROUNDUP(B6*96,0)/96
Hourly Rate: $150
Total Invoice $1968.75 =B7*24*B8

Example of NON-working sheet: Everything is correct except for how the round presents. It's adding correctly, but it should say "33:30"

Time Formulas
10:00
8:00
15:00
0:25
TOTAL: 33:25 =TEXT(SUM(B2:B5), "[h]:mm")
ROUND (nearest 15 min) 9:30 =ROUNDUP(B6*96,0)/96
Hourly Rate: $105
Total Invoice: $3517.50 =B7*24*B8

Upvotes: 1

Views: 29

Answers (1)

Lord Z
Lord Z

Reputation: 9

There are far better experts here, however I will take a stab at it.

First, change the cell format to "Duration" (it will display "HH:MM:SS", if you want to have it as just HH:MM, change it to "Custom date and time" then delete the "second" block and the last ":")

Then try the following changes.

Time Duration Formulas
10:00
8:00
15:00
0:25
TOTAL: 33:25 =sum(F2:F5)
ROUND (nearest 15 min) 33:30 =CEILING(F6,"00:15")
Hourly Rate: $105
Total Invoice: $3517.5 =F8*F7*24
Time Duration Formulas
2:00
10:00
0:35
6:00
TOTAL: 18:35 =SUM(K2:K5)
ROUND (nearest 15 min) 18:45 =CEILING(K6,"00:15")
Hourly Rate: $150
Total Invoice $2812.5 =K8*K7*24

Ignore the "K" and "F", those were just the cells I used while working out the answer.

Upvotes: 0

Related Questions