Datediff with a difference (Not sure its possible)

[askewrj]

I would like to calculate the time diffrence between 2 dates in DD/MM/YYYY HH:MM format which is easy enough. Howver I need to apply this agains a service delivery model:

e.g. date 1 is 01/12/2010 09:00 and date 2 is 02/12/2010 09:00 so the diffrence is 24:00 hrs What I need to take into account is that between 7PM and 7AM our helpdesk is closed so I do not want to count those hours, so the formula should resolve 12 hours not 24. Also I need the same functionality for 2 dates spanning a weekend.

Not sure if its possible? Any help would be appriciated.

Cheers

 

[barry houdini]

If you have start time/date in A2 and end time date in B2 then you can use this formula in C2

=(NETWORKDAYS(A2,B2)-1)*("17:00"-"7:00")+MOD(B2,1)-MOD(A2,1)

assuming that the times are always within working hours.

custom format C2 as [h]:mm

 

[askewrj]

Barry, your an excel genius, thats certainly helpdes out for the calls that span those hours. Any ideas how I would go about doing the same for ommitting weekends. I assuem thats way more complex as you have to determine what day of the week the dates are etc.

If not no worried thats saved a huge chunk of my time. Cheers Rob

 

[barry houdini]

The formula I suggested already omits the weekends (NETWORKDAYS counts only Monday-Friday days between two dates). If you want you can also include a holiday range in NETWORKDAYS so you can also exclude holidays that you define

 

[desert_dweller5]

Dear Mr. Houdini:
Is it possible to get the network days function to shift where the weekend is? for example living in a country that has thursday friday as the weekend and not saturday sunday? is that possible? thanks.

 

[barry houdini]

Yes, you can "offset" the days so if Thu/Fri is the weekend you can use

=NETWORKDAYS(A2+2,B2+2)

....or if you have Excel 2010 you can use NETWORKDAYS.INTL function which lets you customise the weekend days