Help with Time/Date calculation to exclude evening and weekends between dates

[t.powley]

Hi all,

I have some knowledge in working with date and time within excel, but now stuck with a problem, I have to calculate the total hours and minutes a call was open for during working hours only, the call could be open for only a few minutes or it could be open for days/months.

Our daily working hours start from 8:00 to 16:10 - Monday to Friday*

*If possible and you know how to, our weekends start from Friday at 15:40 until Monday 8:00


Call Open date is in Column B
Call Closed Date is in Column H


Example of current cell format for B and H: 27/02/2015 15:22
When the cell format is change to general it shows 42062.64028 (So we know excel is recognising these as date/time)

The final output can be in excel date format or as a general number as long as it has Days, Hours, Minutes are shown.

I know this is big ask and really appreciate any help.

I am using Excel 2010 but will be upgrading to 2013 at some point in the future.

 

[barry houdini]

Hello Tom,

You say that working hours are 08:00 to 16:10 but the weekend starts at 15:40 on Friday - is one of those wrong?

I think you can do this. Are you counting all the hours from 08:00 on Monday to 15:40 (or 16:10) on Friday? Can start and end times be outside those hours, e.g. start on a Sunday or end at 18:00 on Friday?

 

[t.powley]

Hi Barry,

Thanks for the reply,

Hello Tom,

You say that working hours are 08:00 to 16:10 but the weekend starts at 15:40 on Friday - is one of those wrong?

I think you can do this. Are you counting all the hours from 08:00 on Monday to 15:40 (or 16:10) on Friday? Can start and end times be outside those hours, e.g. start on a Sunday or end at 18:00 on Friday?

Just to clarify, the working days are as follows:

Mon, Tue, Wed, Thurs: Count hours from 08:00 until 16:10
Fri: Count hours from 08:00 until 15:40

I’m not too worried if Friday has the same end time as the rest of the week (if this make the formula easier to produce) because any calls open for longer than their allocated time will manually be checked so this can then be accounted for.

Any help in the right direction would be greatly appreciated.

Thanks.

 

[barry houdini]

Assuming that Open/Closed times/dates are always within working hours then you can use this basic formula if working hours are the same for all 5 days

=(NETWORKDAYS(B2,H2)-1)*("16:10"-"8:00")+MOD(H2,1)-MOD(B2,1)

format result cells as [h]:mm to see the result as total hours and minutes

.......but to take into account the different Friday finish time you can use this version:

=SUMPRODUCT(INT((WEEKDAY(B2-{2,3,4,5,6})+H2-B2)/7),{"16:10","16:10","16:10","16:10","15:40"}-"8:00")+MOD(H2,1)-MOD(B2,1)-IF(WEEKDAY(H2)=6,"15:40","16:10")+"8:00"

 

[t.powley]

Hi Barry,

Sorry for the delay in replying but just wanted to say a big Thank You.

The first formula works perfectly, I am now using it on my spread sheet:

=(NETWORKDAYS(B2,H2)-1)*("16:10"-"8:00")+MOD(H2,1)-MOD(B2,1)

-------------------

To get around any issues of calls opened and closed out of working hours I have produced the following formula, took me a while but got there:
The formula that I produced does the following:

- Check to see if the call is opened before 08:00 and if it is, change it to 08:00 of that day.

- Check to see if the call is opened after 16:10 and if it is, change it back to 16:10 of that day.

- Check if the call is opened after 15.40 on Friday and if it is, change time & date to 08:0 the following Monday

- Check if the call is opened on Saturday or Sunday and if it is, change time & date to 08:0 the following Monday

=IF(WEEKDAY(B2,1)=6,IF((B2-ROUNDDOWN(B2,0))>=0.33333,IF((B2-ROUNDDOWN(B2,0))<=0.65278,B2,(ROUNDDOWN(B2,0)+3.33333)),(ROUNDDOWN(B2,0)+0.33333)),IF(WEEKDAY(B2,1)=7,(ROUNDDOWN((B2+2),0)+0.33333),IF(WEEKDAY(B2,1)=1,(ROUNDDOWN(SUM(B2+1),0)+0.33333),IF((B2-ROUNDDOWN(B2,0))>=0.33333,IF((B2-ROUNDDOWN(B2,0))<=0.673611112,B2,(ROUNDDOWN(B2,0)+1.33333)),(ROUNDDOWN(B2,0)+0.33333)))))

This formula works and no doubt that there is a more elegant or efficient way of doing what I want it to do. The main thing was, I enjoyed the challenge of creating this formula.

-------------------

I tested your second formula but ran into an issue:

=SUMPRODUCT(INT((WEEKDAY(B2-{2,3,4,5,6})+H2-B2)/7),{"16:10","16:10","16:10","16:10","15:40"}-"8:00")+MOD(H2,1)-MOD(B2,1)-IF(WEEKDAY(H2)=6,"15:40","16:10")+"8:00"

There is an issue where if the close date is less than 24 hours from open then the result would not be correct, an example:

Create Date (B2) 03/03/2015 09:00
Close Date (H2) 04/03/2015 08:00
Result would return a negative number : -0.041666667

Once again, Thank you for helping me achieve what I need to do, this is going to save hours of work. still lots to learn but I enjoy the challenge.
Tom

 

[barry houdini]

I tested your second formula but ran into an issue:

Hello Tom,

Thanks for testing! Yes, you are right - the formula needs some extra INT functions otherwise it won't work correctly, it can fail even if the period is longer than 24 hours - this version should work in all cases where start and end times are within working hours:

=SUMPRODUCT(INT((WEEKDAY(B2-{2,3,4,5,6})+INT(H2)-INT(B2))/7),{"16:10","16:10","16:10","16:10","15:40"}-"8:00")+MOD(H2,1)-MOD(B2,1)-IF(WEEKDAY(H2)=6,"15:40","16:10")+"8:00"