0

I am trying to subtract hours from a time, but it's giving me strange results

A1 = 06:00:00 (manual entry, hours to deduct)
B1 = 00:30:00 (=C1-A1, start time)
C1 = 06:30:00 (manual entry, end time)

This works fine and continues to work as long as the difference is 6 hours or less.

Why do I get hashes in B1 if A1 is 07:00:00 for example, or if C1 is 05:00:00?

Improve this question
0

1 Answer 1

Reset to default
1

In your case you can't extract more hours then you put in.
In your second example you are trying to subtract 7 hours (A1) from 6 (C1).
In your third example you are trying to subtract 6 hours (A1) from 5 (C1).

If you mean that A1 is your start-time of the day and C1 is your end-time of the day
(where the end-time could be the next day) you should use the formula =C1-A1+IF(C1<A1,1).
That way there is a day added to your end-time if the end-time is lower than your begin-time.

Also make the format of your cells [H]:MM so the hours are displayed correctly.

Improve this answer

You must log in to answer this question.

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