Calculate Working days and time between two days in excel

0
votes

I have written below excel formula to calculate working hours between two dates*(excluding sat and Sunday)*.

it works great if the start and end date is between Monday to Friday . if both days includes sat or Sunday then it raises error .

I'm looking help if the start date is in Saturday or Sunday it should take next business day as a start day and if end date is in Saturday or Sunday it should take previous business day as a end day.

Below is the example and my workings.Then it should return result in h:mm format

Startdate        :   enddate          :   Working hours(exc sat and sun)
9/28/2013 2:20   :   9/29/2013 2:39   :   Both
9/29/2013 2:39   :   9/29/2013 2:39   :   Both
9/29/2013 2:39   :   9/27/2013 13:18  :   StartDate
9/27/2013 13:18  :   9/29/2013 2:22   :   Enddate
10/1/2013 21:22  :   10/2/2013 0:33   :   3.10
9/30/2013 2:22   :   9/30/2013 5:22   :   3.00

and formula in c2 

=IF(OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7,WEEKDAY(B2)=1,WEEKDAY(B2)=7),IF(AND(OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7),OR(WEEKDAY(B2)=1,WEEKDAY(B2)=7)),"Both",IF(OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7),"StartDate","Enddate")),TEXT(B2-A2-(NETWORKDAYS.INTL(A2,B2,"0000000")-NETWORKDAYS(A2,B2)),"D")*24+TEXT(B2-A2-(NETWORKDAYS.INTL(A2,B2,"0000000")-NETWORKDAYS(A2,B2)),"H.MM"))

Let me know if you need any help

1
excelexcel-formula
How many working hours are there in a day? – ttaaoossuuuu

1 Answers

2
votes

To calculate whole working days between two dates, excluding those dates:

=MAX(NETWORKDAYS.INTL(A1+1,B1-1),0)

To calculate working hours (remaining till the end of the day) for the first date:

=IF(WEEKDAY(A1,2)<=5,ROUNDDOWN(A1+1,0)-A1,0)

To calculate working hours (remaining till the end of the day) for the last date:

=IF(WEEKDAY(B1,2)<=5,ROUNDDOWN(B1+1,0)-B1,0)

Sum these up and here you go.