How calculate difference between [t]:mm-format and general format

L

Lehmanns

Excel 2007. Challenge: I have a calculated value in [h]:mm-format "37:00"
(summing up the number of worked hours for a month) and a single value in
general format "80" (the total number of expected working hours for the
month).
I need to calculate the difference via a formula. I having "=g34-e40" for
the "80 minus 37:00" which should be 43. But obviously the two formats makes
a value of either "#############" or "-78.46", depending on the format of the
resulting cell.
Have tried with the CONVERT and INT-function, but no luck...
"=(G34-INT(G34))*24" made my 37:00 comes to 13, but not 37 (a difference of
24 hours though)
Can You assist ?
 
W

ward376

Enter the 80 as hours - 80:00.

Dates and times in Excel are just formatted numbers. Dates to the left
of the decimal and times to the right.

80 hours = 3.33333333333333 (3 and 1 third days)

Excel won't display negative time/date values. (######) Make sure you
subtract the larger from the smaller if you're going to display the
result as a time/date.

Cliff Edwards
 
W

ward376

subtract the larger from the smaller

Make that - subtract the smaller from the larger
 
D

David Biddulph

=G34/24-E40 if you want to format the answer as time
=G34-E40*24 if you want to format the answer as number or general in hours.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top