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(IN T(StartDt),Holidays,0)))),0,ABS(IF(INT(StartDt)=IN T(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

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.

--
Regards,
Tom Ogilvy


"John Pierce" wrote in message
oups.com...
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(IN T(StartDt),Holidays,0)))),
0,ABS(IF(INT(StartDt)=INT(EndDt),ROUND(24*(EndDt-StartDt),2),(24*(DayEnd-Day
Start)*(MAX(NETWORKDAYS(StartDt+1,EndDt-1,Holidays),0)+INT(24*(((EndDt-INT(E
ndDt))-(StartDt-INT(StartDt)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+M
OD(ROUND(((24*(EndDt-INT(EndDt)))-24*DayStart)+(24*DayEnd-(24*(StartDt-INT(S
tartDt)))),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