Subtracting 37 hours from a cell containing format [h]:mm

  • Thread starter Thread starter stainless
  • Start date Start date
S

stainless

I know this should be easy but I have failed so far to write a simple formula in a cell to subtract 37 hours from another cell value in the format [h]:mm

For example, cell B2 is [h]:mm containing the value 39:00. I want C2 to show 2:00 i.e. subtract 37 hours

Likewise, if B2 is 24:00, I want C2 to show -13:00, using the same subtraction method.

I have been unable so far to get either the formula right or indeed the format of C2, although I did assume this format would be also [h]:mm.

Any ideas?
 
Hi,

Am Tue, 18 Dec 2012 06:11:29 -0800 (PST) schrieb stainless:
I know this should be easy but I have failed so far to write a simple formula in a cell to subtract 37 hours from another cell value in the format [h]:mm

For example, cell B2 is [h]:mm containing the value 39:00. I want C2 to show 2:00 i.e. subtract 37 hours

Likewise, if B2 is 24:00, I want C2 to show -13:00, using the same subtraction method.
Click to expand...

try:
=IF(B2<37/24,"-"&TEXT(37/24-B2,"[h]:mm"),B2-37/24)


Regards
Claus Busch
 
Thanks for that. I did have to add another "If" condition to allow for exactly 37 hours but other than that, the formula appears to have worked fine.

Cheers
 
Back
Top