12:00 AM

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JP - 13 Jan 2008 20:34 GMT

I have the time in for one column and the time out in the adjacent
column. I then use this formula to subtract one time from the other:

=IF(C43=0,0,C43-B43+(C43<B43))

It works fine, but I just noticed that if the time out is exactly
12:00 am it gives a zero value. So,

10:00 pm 12:00 am reads 0 in stead of 2:00.

Can't figure out this one.

Reply to this Message

Pete_UK - 13 Jan 2008 20:53 GMT

Have you formatted the cell as a time? Perhaps it is set to General.

Hope this helps.

Pete

> I have the time in for one column and the time out in the adjacent
> column. I then use this formula to subtract one time from the other:
[quoted text clipped - 7 lines]
>
> Can't figure out this one.

Reply to this Message

joeu2004 - 13 Jan 2008 20:55 GMT

> I have the time in for one column and the time out in the adjacent
> column. I then use this formula to subtract one time from the other:
[quoted text clipped - 4 lines]
> 12:00 am it gives a zero value. So,
> 10:00 pm 12:00 am reads 0 in stead of 2:00.

Because that is what your IF() function told it to do. Note that
12:00 AM is stored as a zero. If you did not test for C43=0, C43-B43+
(C43<B43) would have given you exactly what you want (properly
formatted).

Why are you testing C43=0 in the first place? That is, what condition
are you trying to exclude?

I suspect you should test for C43="".

> Can't figure out this one.

When I cannot figure such things out, I change the format of the
cell. In this case, choosing the Number format might have been
sufficient. Often, I choose the Scientific format.

Reply to this Message