Networkdays including Saturday?

[phil.evans]

Hello

I need to calculate the total WORK-hours (09:00-17:30) between two
date/time-stamps;
Including Saturdays but excluding Sundays & Holidays.

I can get this working excluding Saturdays using NETWORKDAYS however
the call centre work six days a week Mon-Sat.

Can anyone help???

Phil

 

[Bob Phillips]

=NETWORKDAYS(C1,C2)+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(C1&":"&C2)))=6))

 

[Bob Phillips]

That should be = 7 not = 6, and I missed the holidays bit.

=NETWORKDAYS(C1,C2,holidays)+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(C1&":"&C2)))=7),--(NOT(ISNUMBER(MATCH(ROW(INDIRECT(C1&":"&C2)),holidays,0)))))

 

[phil.evans]

Hi Bob

From what I can see that sum only works out the days not the hours,
here is the formula I am currently using:

A1=dd/mm/yyyy/hh:mm
A2=dd/mm/yyyy/hh:mm

DayEnd= 17:30:00
DayStart= 09:00:00
HolidayList= "Currently Blank"

=(NETWORKDAYS(A1,A2,HolidayList)-1)*(DayEnd-DayStart)+MOD(A2,1)-
MOD(A1,1)

Phil

 

[Bob Phillips]

Just take off one if the start date is a workday, another if end date is a
workday, and add on the hours in those days.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



Hi Bob

From what I can see that sum only works out the days not the hours,
here is the formula I am currently using:

A1=dd/mm/yyyy/hh:mm
A2=dd/mm/yyyy/hh:mm

DayEnd= 17:30:00
DayStart= 09:00:00
HolidayList= "Currently Blank"

=(NETWORKDAYS(A1,A2,HolidayList)-1)*(DayEnd-DayStart)+MOD(A2,1)-
MOD(A1,1)

Phil

 

[phil.evans]

Bob

Is there a formula that will determine this and calculate accordingly?

Phil