Excel Calculations for TIME

[mimikat]

Hello,

I am trying to keep track off time off for employees and I need the calculations on my spreadsheet to calculate only up to 59 and then once 60 is hit, I need it to calculate to 1 hour.

My current spreadsheet is calculating up to 99 before hitting the 1hour.

I hope this makes sense.

PLEASE HELP!

 

[shg]

For example, ...?

 

[mimikat]

For example:

On my current spreadsheet if I enter 1.45hr for January and then .50min. for February the total calculates to: 1.95

However, I need it to calculate an hour once 60 is hit. Given this, for my above example I would need it to calculate to: 2.35hr.

 

[shg]

Enter the times as 1:45 and 0:50

 

[Dr. Demento]

Alternatively (and more complicated-ly-ier), enter time as decimal (1.30 = 1 hour 30 minutes), create two helper rows (one for Hour, one for Minutes)
Hours =TRUNC(A1,0)
Minutes =MOD(A1,SIGN(A1))/0.6 <-- dividing by 0.6 converts the minutes to fractions of an hour.

Sum both columns, and the total is:
=TRUNC(SUM(both columns),0) + TRUNC(MOD(SUM(both columns),1), 2) and it's in the same format you started with.

How's that for complicated?? shg's way is mo' betta!

 

[mimikat]

Thank you! This works but when the numbers calculate to 24:00 it turns to 0:00

Any idea how to avoid this?

I need it to be able to calculate to 24:00 without turning to 0:00 and continue calculating the hours 25:00, 26:00. etc.,

 

[shg]

Format the result as [h]:mm

 

[mimikat]

Your Awesome. Thank you SO much!

 

[shg]

You're welcome.