CODEWITHSUNDEEP
excelexcel-formula
Stefan Meyer
Stefan Meyer

Reputation: 928

Excel: Substracting time into negative

I have times entered in an Excel sheet:

A1:  08:00
A2:  07:00
A3:  =A2-A1

I expect to see "-01:00" as the result, but I get "##############" For positive results it works.

What do I have to do to get negative hours?

EDIT 1: There must be an easy solution without cascading formulas, etc. I just found a Worksheet in the internet where it seems to work (Cell K10):

Formula in Cell K10

The user defined formatting for K10 is not very special: [hh]:mm;[Red]- [hh]:mm

Upvotes: 1

Views: 70

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

One option uses the TEXT function:

=IF(A2<A1, TEXT(ABS(A2-A1), "-h:mm:ss"), TEXT(ABS(A2-A1), "h:mm:ss"))

This is a well known problem in Excel, q.v. the documentation.

Upvotes: 2

Related Questions