Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 31
Default Number of Saturdays within a range of dates

What, if any, formula can I use to count the number of Saturdays between 2
dates?
Thanks!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 49
Default Number of Saturdays within a range of dates

This is a tricky blighter, heres my answer

part a part b part c
part d
=INT((1+G23-G22)/7)+
IF(WEEKDAY(1+G23-7*INT((G23-G22)/7))<=MOD((1+G23-G22),7),1,0)

g23 is end date, g22 is old date

Explanation

part a works out the number of whole weeks in the date range, they are
assumed to be inclusive dates which is why I add 1

part c finds out, working backwards from the end date the number of whole
weeks we found a, what day of the week we get to.

Part d establishes how many days we had left over in part a

Part b, the if, compares the day of the week we worked back to against the
number of days left over to see if we can include another saturday

QED :)



"Ruben" wrote:

What, if any, formula can I use to count the number of Saturdays between 2
dates?
Thanks!

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 751
Default Number of Saturdays within a range of dates

On May 30, 5:55 pm, Ruben wrote:
What, if any, formula can I use to count the number of Saturdays between 2
dates?
Thanks!


Start date in D1, End date in D2:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(D1&":"&D2)))=7))

HTH
Kostis Vezerides

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default Number of Saturdays within a range of dates

start date in A1 and end date in A2

=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)= 6))


--
Regards,

Peo Sjoblom


"Ruben" wrote in message
...
What, if any, formula can I use to count the number of Saturdays between 2
dates?
Thanks!



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 32
Default Number of Saturdays within a range of dates

Hi Ruben,

With
A1=Start Date and
A2=End Date,

=INT((A2-WEEKDAY(A2-6)-A1+8)/7)

Regards,

Daniel M.

"Ruben" wrote in message
...
What, if any, formula can I use to count the number of Saturdays between 2
dates?
Thanks!



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
Identifying unique dates within a range of cells containing dates cdavidson Excel Discussion (Misc queries) 0 October 12th 06 08:19 PM
Calculate the number of Saturdays or Sundays between 2 dates? Jim Long Excel Discussion (Misc queries) 1 November 1st 05 08:13 PM
Need number of Saturdays and number of Sundays between 2 dates Class316 Excel Worksheet Functions 1 June 10th 05 02:47 AM
count number of years 2003 in a range of dates Stan Altshuller Excel Worksheet Functions 2 May 3rd 05 07:15 PM
Count the number of dates in a range that are earlier than today. DeborahS Excel Worksheet Functions 1 March 9th 05 02:54 AM


All times are GMT +1. The time now is 06:13 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"