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.

What is the format of the two times?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Darren" wrote in message
...
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.

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

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

- John

John Michl Wrote:
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.


--
mward04
------------------------------------------------------------------------
mward04's Profile: http://www.excelforum.com/member.php...o&userid=32407
View this thread: http://www.excelforum.com/showthread...hreadid=500710

Darren,

Try this

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Darren" wrote in message
...
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.

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)


--
Regards

Roger Govier


"Darren" wrote in message
...
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.