Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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

=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 '14 at 20:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .