Calculating how many Saturdays there are between two given dates

[tony.lipschitz]

As a Human Resources Manager, I need to calculate how many working
days leave each employee takes.
As some staff work a 5-day week I have used the formula,
=NETWORKDAYS(A1,B1,O1:O15) where A1 is the first day of leave and B1
is the last day of leave. O1 to O15 list public holidays which the
formula then deducts.

However I want to add a column for staff who work a six-day week with
a formula which will count the number of Saturdays in this same period
( adding the two results will give me how many working days 6-day-a-
week employees take)

Can any one please help?

 

[Ron Coderre]

Try this:

With
A1: (the start date)
A2: (the end date)

This formula counts the number of a specific weekday within that range
=SUM(INT((WEEKDAY(A1-x)+A2-A1)/7))

Note: Replace "x" with one of these values: 1=Sun, 2=Mon.....7=Sat

To count Saturdays....use this:
=SUM(INT((WEEKDAY(A1-7)+A2-A1)/7))

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

 

[Peo Sjoblom]

Start date in A1, end date in B1


=SUM(INT((B1-WEEKDAY(A1+1-{2;3;4;5;6;7})-A1+8)/ 7))

will return a count of all days except Sundays

if you just want to count Saturdays


=SUM(INT((WEEKDAY(A1-7)+B1-A1)/7))

 

[Bob Phillips]

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=7))

--
---
HTH

Bob

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

 

[Sandy Mann]

Peo,

=SUM(INT((WEEKDAY(A1-7)+B1-A1)/7))

Isn't WEEKDAY(A1-7) the same as WEEKDAY(A1)?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Peo Sjoblom]

Yes it is, however it is done to select the different weekdays you want to
count,

WEEKDAY(A1-1)

would count Sundays, subtract 2 for Mondays and so on and from a pedagogical
standpoint and even practical standpoint it doesn't make any sense to remove
it for Saturdays or change other parts of the formula. At least there is a
pattern doing it this way.



Peo

 

[Sandy Mann]

Yes it is, however it is done to select the different weekdays you want to
count,
Ah!

..................................................... and from a
pedagogical standpoint

The things you learn in these newsgroups!

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk