Calculating how many Saturdays there are between two given dates
 

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?

Answer: Calculating how many Saturdays there are between two given dates
 

To count the number of Saturdays between two dates, use the following formula:

Here's how it works:

1. INT((B1-A1)/7)*2 calculates the number of full weeks between the two dates and multiplies it by 2 to get the number of Saturdays in those weeks.
2. IF(WEEKDAY(A1)<=7-MOD(B1-A1,7),1,0) adds 1 if the start date is on a Saturday or a day before the last Saturday before the end date.
3. IF(WEEKDAY(B1)=MOD(B1-A1,7)+1,1,0) adds 1 if the end date is on a Saturday or a day after the first Saturday after the start date.

To add this to your existing formula, you can create a new column and use the above formula to calculate the number of Saturdays for each employee. Then, you can add the two results (number of working days and number of Saturdays) to get the total number of days of leave taken.

Calculating how many Saturdays there are between two given dates
 

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


" wrote:

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?

Calculating how many Saturdays there are between two given dates
 

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))


--
Regards,

Peo Sjoblom





wrote in message
ups.com...
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?

Calculating how many Saturdays there are between two given dates
 

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

--
---
HTH

Bob

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



wrote in message
ups.com...
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?

Calculating how many Saturdays there are between two given dates
 

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


Replace @mailinator.com with @tiscali.co.uk


"Peo Sjoblom" wrote in message
...
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))


--
Regards,

Peo Sjoblom





wrote in message
ups.com...
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?

Calculating how many Saturdays there are between two given dates
 

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" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Peo Sjoblom" wrote in message
...
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))


--
Regards,

Peo Sjoblom





wrote in message
ups.com...
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?

Calculating how many Saturdays there are between two given dates
 

"Peo Sjoblom" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Peo Sjoblom" wrote in message
...
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" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Peo Sjoblom" wrote in message
...
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))


--
Regards,

Peo Sjoblom





wrote in message
ups.com...
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?