Calculating time interval (some across midnight)

I need to calculate two columns of time. Most entries fall within the
same day's 24 hour limit, so a simple forumla "=b1-a1" will do. But a
few in the 2nd column will cross midnight.

I could only find tips on formatting the result of such substracting in
the Excel Help file. The example used falls within the same day.

The example used in the Help file has both the date AND the time in the
same cell: "mm/dd/yy hh:mm." In both columns of mine, the time is
formatted as "hh:mm:ss" only. So I changed an entry to the same format
used in the Help file, which didn't seem to make a difference.

Thanks in advance,
Adam T.

Since XL stores times as fractional days, adding 1 to the later time
will allow correct interval calculation when the interval spans
midnight. One way to do that is to use XL's internal coercion of
TRUE/FALSE values to 1/0 respectively in math operations:

A1: start time
B1: end time
C1: =B1 - A1 + (B1<A1)

an equivalent method is to use the MOD() function:

C1: =MOD(B1-A1,1)

In article <1137518835.493409.123680@f14g2000cwb.googlegroups.com>,
a121turner@lycos.com wrote:

> I need to calculate two columns of time. Most entries fall within the
> same day's 24 hour limit, so a simple forumla "=b1-a1" will do. But a
> few in the 2nd column will cross midnight.
>
> I could only find tips on formatting the result of such substracting in
> the Excel Help file. The example used falls within the same day.
>
> The example used in the Help file has both the date AND the time in the
> same cell: "mm/dd/yy hh:mm." In both columns of mine, the time is
> formatted as "hh:mm:ss" only. So I changed an entry to the same format
> used in the Help file, which didn't seem to make a difference.
>
> Thanks in advance,
> Adam T.

JE:

Thank you for your quick response. I applied your method and it appears
to fix the problem!

Adam.

If cell A1 is your starting time, cell B1 is your ending time, try this
formula in cell C1: =IF(A1>B1, B1-A1+24, B1-A1)

"a121turner@lycos.com" wrote:

> I need to calculate two columns of time. Most entries fall within the
> same day's 24 hour limit, so a simple forumla "=b1-a1" will do. But a
> few in the 2nd column will cross midnight.
>
> I could only find tips on formatting the result of such substracting in
> the Excel Help file. The example used falls within the same day.
>
> The example used in the Help file has both the date AND the time in the
> same cell: "mm/dd/yy hh:mm." In both columns of mine, the time is
> formatted as "hh:mm:ss" only. So I changed an entry to the same format
> used in the Help file, which didn't seem to make a difference.
>
> Thanks in advance,
> Adam T.
>
>