Datediff with a difference (Not sure its possible)

askewrj

New Member
Joined
Dec 2, 2010
Messages
5
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Dec 2, 2010
Messages
5
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Apr 8, 2011
Messages
24
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,814
Messages
5,446,654
Members
405,413
Latest member
AlainCar

This Week's Hot Topics

Top