Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Darren
 
Posts: n/a
Default Calculating Time Past Midnight

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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernard Liengme
 
Posts: n/a
Default Calculating Time Past Midnight

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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
John Michl
 
Posts: n/a
Default Calculating Time Past Midnight

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Calculating Time Past Midnight

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.



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default Calculating Time Past Midnight

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.





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
John Michl
 
Posts: n/a
Default Calculating Time Past Midnight

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

- John

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
mward04
 
Posts: n/a
Default Calculating Time Past Midnight


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating Difference Between Start Date & Time And End Date & Ti Samwar Excel Discussion (Misc queries) 2 December 19th 05 01:42 PM
Calculating a time sheet? tb Excel Worksheet Functions 4 August 4th 05 09:25 AM
I need help with a formula calculating time Mark Excel Discussion (Misc queries) 2 April 27th 05 10:31 AM
unmet challenge boris Excel Worksheet Functions 2 March 16th 05 03:13 PM
calculating with a time format cell Mahnaz Excel Worksheet Functions 1 December 13th 04 11:21 AM


All times are GMT +1. The time now is 03:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"