Formula required to calculate Time difference between Total Hours and Hours Worked

Hello All,

I am using Excel 2013 and I am stuck with a formula for time difference as shown in the attached sample.

I wish to calculate the difference between Total Hours (Col B) and Worked Hours (Col C) and display the result in Col D in in hh:mm format.

Any help would be greatly appreciated.

TIA

You have total hours input as a number of hours, but then worked hours input as a number of days (formatted to show as number of hours). It would be preferable to fix this so they have the same underlying value, but if that's not possible then:

=B2/24-C2

Will give you the difference (as a number of days). You can format this the same as column C, although it will not deal with negative numbers (e.g. the last 4 rows of your example). I don't know what you want to show here.

Dear Nick,
Thanks for the prompt response.

As mentioned by you, it does not work for the last 4 rows.
Any suggestions how can we solve the issue as the employee have put in more time in those months.

Thanks once again for your time and help.

Without using the 1904 time system, Excel cannot display negative times.

One possibility is to calculate all times positive (=ABS(B3-C3)),
but if C3>B3 format the times differently using Conditional Formatting rules, so that you see that it is a negative time.

Furthermore, I would consider calculating the hours everywhere in days (that is independent of showing in hours), then the formulas remain the simplest.

The question is what do you want as a result when they have done overtime? Do you want to show a negative time? Or something else?

IF you want a negative time you could use this formula in D2, copied down:

=IF(B2/24>=C2,TEXT(B2/24-C2,"[h]:mm"),"-"&TEXT(C2-B2/24,"[h]:mm"))

Edit - the downside of this is that the result becomes text, which might be a problem if you then use it for something else.