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 ?

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

subtract the larger from the smaller

Make that - subtract the smaller from the larger

=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.
--
David Biddulph

"Lehmanns" wrote in message
...
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 ?