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?
Answer
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)
No comments:
Post a Comment