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