If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




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 5day 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 sixday 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 6daya week employees take) Can any one please help? 
Ads 
#2




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(A1x)+A2A1)/7)) Note: Replace "x" with one of these values: 1=Sun, 2=Mon.....7=Sat To count Saturdays....use this: =SUM(INT((WEEKDAY(A17)+A2A1)/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 5day 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 sixday 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 6daya > week employees take) > > Can any one please help? > > 
#3




Calculating how many Saturdays there are between two given dates
Start date in A1, end date in B1
=SUM(INT((B1WEEKDAY(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(A17)+B1A1)/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 5day 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 sixday 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 6daya > week employees take) > > Can any one please help? > 
#4




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 5day 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 sixday 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 6daya > week employees take) > > Can any one please help? > 
#6




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(A11) 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(A17)+B1A1)/7)) >> > Isn't WEEKDAY(A17) 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((B1WEEKDAY(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(A17)+B1A1)/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 5day 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 sixday 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 6daya >>> week employees take) >>> >>> Can any one please help? >>> >> >> >> > > 
#7




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(A11) > > 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(A17)+B1A1)/7)) >>> >> Isn't WEEKDAY(A17) 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((B1WEEKDAY(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(A17)+B1A1)/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 5day 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 sixday 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 6daya >>>> week employees take) >>>> >>>> Can any one please help? >>>> >>> >>> >>> >> >> > > > 
Thread Tools  
Display Modes  


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 