Counting hours mins secs between 2 dates and times

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

Hello

In my worksheet I have 4 columns. I have a start dates and end dates
as well as times for start and end. I need to calculate the time
elapsed between the start date and time till the end date and time.
But it must display it as HH, MM, SS. I must show whether it has gone
over 100 hours. That is the urgent level of my time frame
I need it to look something like this

Start Date Start Time End Date End Time Elapsed Time
06/01/2004 7:00 AM 6/08/2004 7:00 PM 144:55:24

I keep coming up with 00's

Thanks
Bob
 
Use

=(C2+D2)-(A2+B2)

, but you'll need to Format -- Cells -- Number -- Custom -- as (type =)

[h]:mm:ss

Rgds,
Andy
 
Hi Andy

It is working for the time but it is not including the date. It is
only showing a 12 hour laps between 7:00 AM and 7:00PM
I have the date formatted as a regular date. Is that correct?

Thanks for helping
Bob
 
No, not a regular date, a custom format of
[h]:mm:ss

Jerry
Hi Andy

It is working for the time but it is not including the date. It is
only showing a 12 hour laps between 7:00 AM and 7:00PM
I have the date formatted as a regular date. Is that correct?

Thanks for helping
Bob

Use

=(C2+D2)-(A2+B2)

, but you'll need to Format -- Cells -- Number -- Custom -- as (type =)

[h]:mm:ss

Rgds,
Andy
Click to expand...
Click to expand...
 
I think I may have mis-explained myself. I would like to calculate the
difference between the start date and time from now. So I want the
elasped time from start date and time till now.

Sorry
Bob
 
Bob,

I think what's been suggested by Andy and Jerry accomplishes what yo
are trying to do.

Taking your example of
Start Date = June 1 2004 (Cell A1)
Start Time = 7 AM (Cell A2)
End Date = June 8 2004 (Cell B1)
End Time = 7 PM (Cell B2)

Calculating Manually - Total Time elapsed = 7 * 24 + 12 = 180 hours

In Excel - Total Time Elapsed (Cell D1) = (B1 + B2) - (A1 + A2)
180:00:00

Just make sure that you go to Format Cells -> Custom -> [h]:mm:ss fo
Cell D1
 
Ok now I understand. I had an error in what I was doing. I was only
getting the time difference between 7am and 7pm which gave me the
12:00:00.

Thanks for taking the time to show me this. I am a little new to excel
so I really appreciate this

Thank you all for you time in helping me
Bob
 
Hello

Now that I am able to calculate the elapsed time. Is there a way to
count it down to zero base on the current time.. 180 is the maxium
time frame to do the log but I would like to be able to have it count
down so I can know the time is getting near. So if it's starts at 180
I can refresh to get the remaining time. I have never used a timer in
excel

Thanks
Bob
 
Back
Top