Calculating Time Past Midnight

[Guest]

I have 4 columns in a worksheet. Column 1 has the date, 2 has the time, 3
has the end date and column 4 has the end time. How can I calculate time
when the time passes the midnight in column 5? There are more than 12,000
rows and it takes forever to manually make the changes.

 

[Bernard Liengme]

What is the format of the two times?

 

[John Michl]

Darren -
Remember that time values are actually a special format of a date. It
is likely that unless specifically entered with the date, the default
date for a time value is 1/0/1900. If you enter 10:00 PM as the
StartTime and 1:00 AM as the EndTime, Excel will think that both are
from the same day which means the EndTime will have a smaller value
than the StartTime. That is why your formula doesn't work when the
time crosses midnight. Here's a fix.

Assuming that you time values are within 24 hours of each other the
following formula will work:

=IF(EndTime<StartTime, 1 + EndTime - StartTime, EndTime - StartTime)

This formula will add a day to the end time so instead of being 1:00 am
on 1/0/1900 it will be 1:00 am on 1/1/1900 (a day later). The result
of the formula is the portion of a 24 hour period that has transpired
between the two times. You'll still need to convert to hours or
minutes.

If you have time periods that span multiple days, you'd take a slightly
different approach. Add a column that contains EndDate. Then your
formula would be:
= (EndDate+EndTime) - StartDate+StartTime)

Hope that helps.

- John Michl

 

[Bob Phillips]

Darren,

Try this

=(C1-A1-1)+(1-B1)+D1

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Roger Govier]

Hi Darren

Since you say you have the dates in column A and C, with Times in B and
D, and presuming the end date can never be before the start date, then
it is simply a question of creating 2 values which are both date and
time, and taking one from the other.

=(C1+D1)-(A1+B1)

 

[John Michl]

I didn't read your message closely enough and missed that you already
have the second date. Therefore, use my second option.

- John

 

[mward04]

Darren -
Remember that time values are actually a special format of a date. It
is likely that unless specifically entered with the date, the default
date for a time value is 1/0/1900. If you enter 10:00 PM as the
StartTime and 1:00 AM as the EndTime, Excel will think that both are
from the same day which means the EndTime will have a smaller value
than the StartTime. That is why your formula doesn't work when the
time crosses midnight. Here's a fix.

Assuming that you time values are within 24 hours of each other the
following formula will work:

=IF(EndTime<StartTime, 1 + EndTime - StartTime, EndTime - StartTime)

This formula will add a day to the end time so instead of being 1:00
am
on 1/0/1900 it will be 1:00 am on 1/1/1900 (a day later). The result
of the formula is the portion of a 24 hour period that has transpired
between the two times. You'll still need to convert to hours or
minutes.

If you have time periods that span multiple days, you'd take a
slightly
different approach. Add a column that contains EndDate. Then your
formula would be:
= (EndDate+EndTime) - StartDate+StartTime)

Hope that helps.

- John Michl

What about just entering 27:00 for 3:00AM the next day? It still
registers as 3:00AM 1/1/1900 and you can then use another formula in
the current cell.