Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identifying unique dates within a range of cells containing dates | Excel Discussion (Misc queries) | |||
Calculate the number of Saturdays or Sundays between 2 dates? | Excel Discussion (Misc queries) | |||
Need number of Saturdays and number of Sundays between 2 dates | Excel Worksheet Functions | |||
count number of years 2003 in a range of dates | Excel Worksheet Functions | |||
Count the number of dates in a range that are earlier than today. | Excel Worksheet Functions |