# Calculate between two dates for diffrent between Business Hour for Weekdays and Weekends

#### r.sunkum

##### New Member

I have an Scineario where in i have to clculate business hours from Monday to Friday (08:00 To 22:00) and on Saturday - Sunday(08:00 To 18:00).

Iam able to calculate for Monday to Friday using follwoing formula
NETWORKDAYS(A3,B3,H_D)*(eT-sT)-(NETWORKDAYS(A3,A3,H_D)*MAX(0,MIN(eT,MOD(A3,1))-sT))-(NETWORKDAYS(B3,B3,H_D)*MAX(0,eT-MAX(MOD(B3,1),sT)))

but Iam Not able to get the logic for Weekend calculation

Thanks & Regards
r.sunkum

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### r.sunkum

##### New Member
For Example

A1= Start Date & Time Example ( 19/11/2010 05:05:40 PM)
B1= End Date & Time Example (28/12/2010 10:41:49 AM)

D1= Start Time: 08:00
D2= End Time: 22:00

E1= Start Time: 08:00
E2= End Time: 18:00

#### barry houdini

##### MrExcel MVP
Just to clarify.....

Can the start/end times be outside the business hours, e.g. could there be a start time at 07:00 on a Wednesday or a finish time at 21:00 on Saturday?

Do you want to exclude holidays too...or do they have different hours?

Which version of Excel are you using?

#### r.sunkum

##### New Member
Hi Houdini,

Can the start/end times be outside the business hours, e.g. could there be a start time at 07:00 on a Wednesday or a finish time at 21:00 on Saturday?

> Yes the start/end time could be outside the business hours.

Do you want to exclude holidays too...or do they have different hours?

>There are no holidays

Which version of Excel are you using?

> Iam using Excel 2007.

#### r.sunkum

##### New Member
Hi All,

Here is the logic for the weekend calculation

A1= Start Date & Time Example ( 19/11/2010 05:05:40 PM)
B1= End Date & Time Example (28/12/2010 10:41:49 AM)

E1= Start Time: 08:00
E2= End Time: 18:00

=(SUM(INT((8-WEEKDAY(B1+{0,1})+B1-A1)/7))*((E2-E1)-(SUM(INT((8-WEEKDAY(A1+{0,1})+A1-A1)/7))*MEDIAN(E2-E1,0,MOD(A1,1)-E1))-(SUM(INT((8-WEEKDAY(B1+{0,1})+B1-B1)/7))*MEDIAN(E2-E1,0,MOD(B1,1)-E2)))))))

Result= 5 in General Format & in [m].00 format it is 7200.00

Calculation how it gets converted from genral format to [m].ss format
24*60 = 1440.00
5/1440 = 7200.00

Last edited:

#### barry houdini

##### MrExcel MVP
Do you need to calculate the weekend minutes separately or would you rather calculate the entire duration in one formula?

I don't think your formula works in all circumstances for the weekend duration......but this version should give you the total minutes for all days

=SUM(INT((WEEKDAY(A1-{0,1})+INT(B1)-INT(A1))/7)*(E\$2-E\$1),INT((WEEKDAY(A1-{2,3,4,5,6})+INT(B1)-INT(A1))/7)*(D\$2-D\$1))-IF(WEEKDAY(A1,2)>5,MEDIAN(0,MOD(A1,1)-E\$1,E\$2-E\$1),MEDIAN(0,MOD(A1,1)-D\$1,D\$2-D\$1))-IF(WEEKDAY(B1,2)>5,MEDIAN(0,E\$2-MOD(B1,1),E\$2-E\$1),MEDIAN(0,D\$2-MOD(B1,1),D\$2-D\$1))

format as before

#### r.sunkum

##### New Member
Hi Houdini,

Yes the formula provided by you works perfectely.

Thanks & Regards
R.Sunkum

Replies
1
Views
655
Replies
6
Views
1K
Replies
4
Views
392
Replies
8
Views
558
Replies
0
Views
235

1,190,857
Messages
5,983,249
Members
439,833
Latest member
CDaviess

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back