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.

 Calculating how many Saturdays there are between two given dates
 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

#1
July 25th 07, 04:26 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 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 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)

#2
July 25th 07, 04:40 PM posted to microsoft.public.excel.worksheet.functions
 Ron Coderre external usenet poster Posts: 698
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)
>
>
>

#3
July 25th 07, 04:54 PM posted to microsoft.public.excel.worksheet.functions
 Peo Sjoblom external usenet poster Posts: 3,268
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)
>
>

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

#5
July 25th 07, 05:09 PM posted to microsoft.public.excel.worksheet.functions
 Sandy Mann external usenet poster Posts: 2,345
Calculating how many Saturdays there are between two given dates

#6
July 25th 07, 07:34 PM posted to microsoft.public.excel.worksheet.functions
 Peo Sjoblom external usenet poster Posts: 3,268
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)
>>>
>>>

>>
>>
>>

>
>

#7
July 25th 07, 10:38 PM posted to microsoft.public.excel.worksheet.functions
 Sandy Mann external usenet poster Posts: 2,345
Calculating how many Saturdays there are between two given dates

 Thread Tools Display Modes Linear Mode

 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 User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 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 07: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 11:42 AM.