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

[r.sunkum]

Business Hour Issue

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

Solution Please

Thanks & Regards
r.sunkum

 

[r.sunkum]

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)

Weekdays Business Hours
D1= Start Time: 08:00
D2= End Time: 22:00

Weekend Business Hours
E1= Start Time: 08:00
E2= End Time: 18:00

The Answer should Comprises of (Weekdays+Weekend Business Hours) in Minutes [m]:ss

 

[barry houdini]

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]

Hi Houdini,

Thank you for replying

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]

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)

Weekend Business Hours
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

 

[barry houdini]

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]

Hi Houdini,

Thank you for replying,

Yes the formula provided by you works perfectely.

Thanks & Regards
R.Sunkum