Remember Me?

 #1     July 25th 07, 04:26 PM posted to microsoft.public.excel.worksheet.functions 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
July 25th 07, 04:40 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 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
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 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
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 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
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 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
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 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
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 2,345
Calculating how many Saturdays there are between two given dates

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Ruben Excel Worksheet Functions 4 May 31st 07 03:50 AM Jim Long Excel Discussion (Misc queries) 1 November 1st 05 07:13 PM [email protected] Excel Discussion (Misc queries) 5 October 26th 05 06:18 PM jeda67 Excel Worksheet Functions 4 August 30th 05 02:14 PM Class316 Excel Worksheet Functions 1 June 10th 05 02:47 AM

All times are GMT +1. The time now is 10:09 PM.