Subtract date/time and get elapses days, hours, minues, etc

Welcome to the board
I think this link can get you started :http://www.cpearson.com/excel/DateTimeWS.htm

Well it makes sense if I can get it to work! Thanks!

Dumb question...but should I be putting in actual dates where it says StartDt or should I be referencing the cell that contains the start date? I think I'm thinking too hard and confusing myself!

Hve you tried putting the date in an then a cell reference perhaps (",)

If you download the sample workbook it might save you some time, it has already been formatted...If your on a tight deadline it might be a great help to you!

Argggg....well i got some to work! The same formula is used for all of these. (First is Time Message was left, Second is Time Recieved)
Assuming 8:30 am - 5:00 pm is work hours, Monday-Friday.

Any thoughts?

These are Wrong
11/30/06 3:30 11/30/06 13:00 0 days 9.5hours
12/4/06 16:00 12/5/06 9:00 1 days 7 hours
12/26/06 14:00 12/27/06 9:00 1 days 5 hours
1/2/07 16:30 1/3/07 9:30 1 days 7 hours
1/3/07 8:15 1/3/07 17:00 0 days 8.75hours

These are Right
1/5/07 17:00 1/8/07 9:30 0 days 1 hours
7/19/07 16:00 7/20/07 11:30 0 days 4 hours
7/10/07 8:00 7/13/07 13:00 3 days 5 hours

IF(AND(INT(B6)=INT(D6),NOT(ISNA(MATCH(INT(B6), HolidayList,0)))),"0 days 0 hours", IF(INT(B6)=INT(D6), "0 days " & ROUND(24*(D6-B6),2)&"hours", MAX(NETWORKDAYS(B6+1,D6-1,HolidayList),0)+INT(24*(((D6-INT(D6))-(B6-INT(B6)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart)))&
" days "&MOD(ROUND(((24*(D6-INT(D6)))-24*DayStart)+(24*DayEnd-(24*(B6-INT(B6)))),2),ROUND((24*(DayEnd-DayStart)),2))&" hours "))

Everyone's help is greatly appreciated!

Do you use the example workbook?
And dont you have your right and wrong the wrong way round?
If i use the example workbook on the example:
1/5/07 17:00 1/8/07 9:30
it returns:

I believe this is right as it doesnt count Saturday and Sunday?....

Chip's formula may be more complex than you need.

Assuming you have start time and date in B6 and end time and date in D6 and both of these times will be within working hours you can use this formula to give the result in hours and minutes, e.g. 10:00 for your example

=(NETWORKDAYS(B6,D6)-1)*("17:00"-"08:30")+MOD(D6,1)-MOD(B6,1)

format result cell as [h]:mm

You can insert a holiday range in the NETWORKDAYS function if you wish

edit:

sorry didn't read your post properly . Given that your start time (B6) can be anytime but D6 will be within business hours try this formula

=(NETWORKDAYS(B6,D6)-1)*("17:00"-"08:30")+MOD(D6,1)-MEDIAN(NETWORKDAYS(B6,B6)*MOD(B6,1),"08:30"+0,"17:00"+0)

again, format as [h]:mm

Chip's formula doesn't work correctly if either time is outside business hours

We only want to assume that a message can be retrieved during business hours but a message can be left at anytime!

Click to expand...

Barry, as I understand it the message can be received at anytime, during, or outside of working hours