Help with adding 24 hour times

[jenwren]

Hello,

I am an administrator working with excel on a regular basis at an itermediate level but am at times baffled by a particular problem with excel!

I would sincerely like some help with the uploaded file. I am trying to create an hours worked spreadsheet in 24 hour time, and have created one with a simple formula to add up the hours worked when someone is working past midnight. This works fine until I attempt to add up the total number of hours worked. I have tried all sorts of variations on formating the cell for this in the custom formatting options [h]:mm, [h]:mm:ss;@ etc. which is supposed to enable this to happen - I will be honest and say I don't know how this formatting works - but nothing is working. I tried this option on a previous spreadsheet which worked fine but as soon as I added in the new formula it stopped adding up correctly. I would very much appreciate some help.

Thank you,

 

[Hui]

Jenren

Your problem is two fold

1. Times are fractions of 1 so 24 Hrs = 1, 12 Hrs = 0.5, 6 hrs = 0.25 etc
So your formula in E2: Should be: =IF(C2>B2,C2-B2,1+C2-B2))-D2

2. Change the Custom Format to [hh]:mm:ss
This will then display 25 hrs as 25 hrs etc

 

[jenwren]

Jenren

Your problem is two fold

1. Times are fractions of 1 so 24 Hrs = 1, 12 Hrs = 0.5, 6 hrs = 0.25 etc
So your formula in E2: Should be: =IF(C2>B2,C2-B2,1+C2-B2))-D2

2. Change the Custom Format to [hh]:mm:ss
This will then display 25 hrs as 25 hrs etc

That worked, thank you so much that is a huge help!