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

[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?

 

[Claus Busch]

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.

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


Regards
Claus Busch

 

[stainless]

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