Time Calculation Function Question
Bobby Pullin
a total of number of hours elapsed.
Example:
Date Time Start Date Time End Elapsed time
22-Sep-99 9/22/99 23:00 9/23/99 1:05 2:05
I have no problem until I exceed a 24 hour period.
Does anyone have a formula that will calculate time elapsed to include hours
and minutes.
Thanks
Bobby
send replies directly to pul...@va.prestige.net
Chip Pearson
Try something like
=(B1-A1)+(B1<A1)
where B1 is the EndTime and A1 is the StartTime. The second part of the
formula adds a 1 (the same as a 24 hour day) when B1 is earlier than A1.
Essentially, this makes the formula equivalent to EndTime_Tomorrow -
StartTime_Today.
If the start and end times are equal, the result is 0. E.g., start and end
at 6AM indicates no work done at all. To make it 24 hours (e.g., start at
6AM Tuesday, end at 6AM Wednesday), change the < to a <=.
For more info about dates and times in Excel than you'll ever really want to
know, see the Dates And Times pages on my web site, starting at
www.cpearson.com/excel/datetime.htm
By the way, this questions isn't really relevant to Templates, so you're not
posting the most relevant group. This is really a formula question, so
you'd be better off to post to the worksheet.functions group. You'll get a
more rapid reply if you post to the (one) group that is most relevant to
your question.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting Services
www.cpearson.com ch...@cpearson.com
"Bobby Pullin" <pul...@va.prestige.net> wrote in message
news:uksK9EZx$GA.250@cppssbbsa03...
David Byrne
Try
= (DayEnd + TimeEnd/24) - (DayStart + TimeStart/24)
David
in article #HTXIxax$GA....@cppssbbsa02.microsoft.com, Chip Pearson at
ch...@cpearson.com wrote on 25/5/00 4:17 AM:
Ramu
crossing the midnight mark:
Assuming Start Time = Cell A1
End Time = Cell B1
Elapsed Time = Cell C1
Formula to calculate elapsed time in Cell C1 should be =MOD(B1-A1,1)
Regards
Ramu