A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Calculating how many Saturdays there are between two given dates



 
 
Thread Tools Display Modes
  #1  
Old July 25th 07, 04:26 PM 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?

Ads
  #2  
Old July 25th 07, 04:40 PM posted to microsoft.public.excel.worksheet.functions
Ron Coderre
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?
>
>

  #3  
Old July 25th 07, 04:54 PM posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
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?
>



  #4  
Old July 25th 07, 04:57 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
external usenet poster
 
Posts: 10,594
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?
>



  #5  
Old July 25th 07, 05:09 PM posted to microsoft.public.excel.worksheet.functions
Sandy Mann
external usenet poster
 
Posts: 2,345
Default 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?
>>

>
>
>



  #6  
Old July 25th 07, 07:34 PM posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
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?
>>>

>>
>>
>>

>
>



  #7  
Old July 25th 07, 10:38 PM posted to microsoft.public.excel.worksheet.functions
Sandy Mann
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?
>>>>
>>>
>>>
>>>

>>
>>

>
>
>



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 07:39 PM.


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