Elapsed Time

J

John Pierce

I have a form with text boxes for input of the Start Date (and Time)
and End Date (and Time) for events and it calculates the total elapsed
time using the formula below which came from Chip Pearson's Excel
site.
When, for example, StartDt = 01/06/2006 15:00, EndDt = 01/10/2006
15:00, DayStart = 7:00, DayEnd = 23:00
The code behind the form writes the above data and the following
formula to the sheet.
ActiveCell.Offset(0, 5).Formula =
"=IF(AND(INT(StartDt)=INT(EndDt),NOT(ISNA(MATCH(INT(StartDt),Holidays,0)))),0,ABS(IF(INT(StartDt)=INT(EndDt),ROUND(24*(EndDt-StartDt),2),(24*(DayEnd-DayStart)*(MAX(NETWORKDAYS(StartDt+1,EndDt-1,Holidays),0)+INT(24*(((EndDt-INT(EndDt))-(StartDt-INT(StartDt)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+MOD(ROUND(((24*(EndDt-INT(EndDt)))-24*DayStart)+(24*DayEnd-(24*(StartDt-INT(StartDt)))),2),ROUND((24*(DayEnd-DayStart)),2))))))
"

I get the correct result of 32.00 hours in this case.

I was wondering, since the formula clearly recognizes that the hours
come from three different days, if the result could be parsed using VBA
to look like:

01/06/2006 3:00 PM - 11:00 PM 8.00 hours
01/09/2006 7:00 AM - 11:00 PM 16.00 hours
01/10/2006 7:00 AM - 3:00 PM 8.00 hours
 
T

Tom Ogilvy

It basically subtracts the start date and time from the end date and time
and subtracts out holidays and weekends. It doesn't return an array of
times by day, so the answer would be no, there is no inherent (to the
formula) way to do that. That isn't to imply that you couldn't write
different formulas to do it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top