Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 0 down vote accepted

The result of


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


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

Your Answer


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.