Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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?

  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Calculating how many Saturdays there are between two given dates

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

Code:
=INT((B1-A1)/7)*2+IF(WEEKDAY(A1)<=7-MOD(B1-A1,7),1,0)+IF(WEEKDAY(B1)=MOD(B1-A1,7)+1,1,0)
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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 698
Default 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?


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default 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?



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default 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?





  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default 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?








  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,345
Default 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?











Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Number of Saturdays within a range of dates Ruben Excel Worksheet Functions 4 May 31st 07 03:50 AM
Calculate the number of Saturdays or Sundays between 2 dates? Jim Long Excel Discussion (Misc queries) 1 November 1st 05 08:13 PM
Calculating number of days between two dates that fall between two other dates [email protected] Excel Discussion (Misc queries) 5 October 26th 05 06:18 PM
I need help calculating dates? jeda67 Excel Worksheet Functions 4 August 30th 05 02:14 PM
Need number of Saturdays and number of Sundays between 2 dates Class316 Excel Worksheet Functions 1 June 10th 05 02:47 AM


All times are GMT +1. The time now is 04:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"