Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am trying to subtract hours to a time that is close to midnight, but excel is outputting all hash (#) symbols complaining of the operation.

Adding time is easy and possible, as described by this first example:

For example, I have a cell with the value 11:00 PM (the column is formatted to this time format). I need to add 2 hours to it and get 01:00 AM. Assuming that cell A1 has the 11:00 PM value, I use =A1+time(2,0,0) and get 01:00 AM, which is what I expect.

However, when I try the reverse of this operation (minusing), excel outputs all hash symbols.

For example, I have a cell with the value 01:00 AM. I need to minus 2 hours to it and get 11:00 PM. I try =A1-time(2,0,0), but all I get are hash symbols from excel.

How can I subtract a number of hours (or minutes) from a time that is very close to midnight?

share|improve this question

1 Answer 1

up vote 0 down vote accepted

The result of

=A1-TIME(2,0,0)

is a negative value if A1 = 01:00, and excel doesn't like negative time values in most cases, hence #####.

Try using MOD function like this

=MOD(A1-TIME(2,0,0),1)

share|improve this answer
    
Works like a charm. Thanks! –  train Mar 30 at 20:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.