Calculating Working Hours inc Sat

[Kris75]

Hi Guys
I'm trying to work out the number of working hours passed between two Date/times.

C13= Start date/time
D13=End date/time
X1= Start of Shift (0800) Mon - Fri
Y1= End of Shift (1800) Mon - Fri

using this Formula - i can calculate how many 'working' hours have passed
in a standard mon - Fri 8 - 6 shift - Which is great

what i need to do is add to this a Saturday Shift which starts at 0900 and Ends 1300

Any Suggestions?

Kris
=(NETWORKDAYS(C13,D13)-1)*($Y1-X1)+IF(NETWORKDAYS(D13,D13),MEDIAN(MOD(D13,1),Y1,X1),Y1)-MEDIAN(NETWORKDAYS(C13,C13)*MOD(C13,1),Y1,X1)

 

[Andrew Poulsom]

To count the number of Saturdays between start date and end date:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(C13)&":"&INT(D13))))=7))

 

[barry houdini]

The formula you are using for Monday to Friday allows the C13 and D13 times to be any time (even outside the MF working hours), I assume you need that (otherwise you can use a simpler formula)?

To add Saturday hours you potentially have to count Saturdays in the period .....but also take account of periods that might start (or end) on a Saturday, to do that you can use this formula to calculate just Saturday hours

=INT((WEEKDAY(C13)+INT(D13)-INT(C13))/7)*(Y$2-X$2)-IF(WEEKDAY(C13)=7,MEDIAN(0,MOD(C13,1)-X$2,Y$2-X$2))-IF(WEEKDAY(D13)=7,MEDIAN(0,Y$2-MOD(D13,1),Y$2-X$2))

assuming Saturday start and end times are in X2 and Y2

You can just add that to your existing formula for a total of all hours

 

[Kris75]

Thank you for you for your help Fellas

that was exactly what i wanted

It works like a dream now.