Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to list dates in a month of particular weekday
MY question is simple, sorry if i'm being stupid.
Can anyone tell me how can i make a list which contains only dates of all the Fridays in a particular month? In a more advanced sense, can I create a list with all the Fridays AND Saturdays of a month? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to list dates in a month of particular weekday
You didn't post a lot of detail about your setup. Assuming the month number
is in A1 and your list of Fridays and Saturdays is to be listed in Column B starting at B1, put these formulas in the indicated cells... B1: =DATE(YEAR(NOW()),$A$1,8)-WEEKDAY(DATE(YEAR(NOW()),$A$1,2)) B2: =DATE(YEAR(NOW()),$A$1,8)-WEEKDAY(DATE(YEAR(NOW()),$A$1,1)) B3: =IF(B1="","",IF(MONTH(B1+7)=$A$1,B1+7,"")) And then copy down the formula that is in B3 down to B10. -- Rick (MVP - Excel) "MasterDragon" wrote in message ... MY question is simple, sorry if i'm being stupid. Can anyone tell me how can i make a list which contains only dates of all the Fridays in a particular month? In a more advanced sense, can I create a list with all the Fridays AND Saturdays of a month? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to list dates in a month of particular weekday
MSDN has a good exaple of finding a day
http://msdn.microsoft.com/en-us/libr...32(VS.60).aspx "MasterDragon" wrote: MY question is simple, sorry if i'm being stupid. Can anyone tell me how can i make a list which contains only dates of all the Fridays in a particular month? In a more advanced sense, can I create a list with all the Fridays AND Saturdays of a month? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to list dates in a month of particular weekday
Just so you don't think those formulas were mystically arrived at, here is
the generic form for calculating the nth such and such day of a month... The generic version of the formula is this... =DATE(Yr,MM,1+7*Nth)-WEEKDAY(DATE(Yr,MM,8-DoW)) where nth is the number you want 1st, 2nd, 3rd etc thus in my first formula 7*1 since you wanted the 1st Friday and where DoW stands for day of the week with Sunday starting with 1 and so on and where I put 6 for Friday. So, if you want the 1st Friday of the month number in A1 it would look like =DATE(YEAR(NOW()),$A$1,1+7*1)-WEEKDAY(DATE(YEAR(NOW()),$A$1,8-6)) which reduces to the first formula I listed. Note: I don't know if this generic formula was original with him or not, but I first saw it posted online by Peo Sjoblom. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... You didn't post a lot of detail about your setup. Assuming the month number is in A1 and your list of Fridays and Saturdays is to be listed in Column B starting at B1, put these formulas in the indicated cells... B1: =DATE(YEAR(NOW()),$A$1,8)-WEEKDAY(DATE(YEAR(NOW()),$A$1,2)) B2: =DATE(YEAR(NOW()),$A$1,8)-WEEKDAY(DATE(YEAR(NOW()),$A$1,1)) B3: =IF(B1="","",IF(MONTH(B1+7)=$A$1,B1+7,"")) And then copy down the formula that is in B3 down to B10. -- Rick (MVP - Excel) "MasterDragon" wrote in message ... MY question is simple, sorry if i'm being stupid. Can anyone tell me how can i make a list which contains only dates of all the Fridays in a particular month? In a more advanced sense, can I create a list with all the Fridays AND Saturdays of a month? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to list dates in a month of particular weekday
Hi,
With a properly formatted date in a1 try this and drag down for the Fridays of that month in A1 The key to Getting the other days of the weekdays lies in the -1 in this bit of the formula ROWS(A$1:$A1)*7)-1 Change the -1 to different values (It appears 3 times) and you'll figure it out =IF(MONTH(($A$1-WEEKDAY($A$1,1))+(ROWS(A$1:$A1)*7)-1)<MONTH($A$1),(($A$1+7-WEEKDAY($A$1+7,1))+(ROWS(A$1:$A1)*7)-1),($A$1-WEEKDAY($A$1,1))+(ROWS(A$1:$A1)*7)-1) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "MasterDragon" wrote: MY question is simple, sorry if i'm being stupid. Can anyone tell me how can i make a list which contains only dates of all the Fridays in a particular month? In a more advanced sense, can I create a list with all the Fridays AND Saturdays of a month? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to list dates in a month of particular weekday
By the way, I gave you a solution for your second question; however, if you
only wanted to list the Fridays by themselves, use the same formula I gave you for B1, but use the formula I gave you for B3 in B2 instead and then copy it down. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... You didn't post a lot of detail about your setup. Assuming the month number is in A1 and your list of Fridays and Saturdays is to be listed in Column B starting at B1, put these formulas in the indicated cells... B1: =DATE(YEAR(NOW()),$A$1,8)-WEEKDAY(DATE(YEAR(NOW()),$A$1,2)) B2: =DATE(YEAR(NOW()),$A$1,8)-WEEKDAY(DATE(YEAR(NOW()),$A$1,1)) B3: =IF(B1="","",IF(MONTH(B1+7)=$A$1,B1+7,"")) And then copy down the formula that is in B3 down to B10. -- Rick (MVP - Excel) "MasterDragon" wrote in message ... MY question is simple, sorry if i'm being stupid. Can anyone tell me how can i make a list which contains only dates of all the Fridays in a particular month? In a more advanced sense, can I create a list with all the Fridays AND Saturdays of a month? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to list dates in a month of particular weekday
On reflection that was a bit misleading because it isn't always a -1 value so
i'll do it for your. The numbers are -1 fri 0 sat 1 sun 2 mon 3 tue 4 wed 5 thu Mike -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, With a properly formatted date in a1 try this and drag down for the Fridays of that month in A1 The key to Getting the other days of the weekdays lies in the -1 in this bit of the formula ROWS(A$1:$A1)*7)-1 Change the -1 to different values (It appears 3 times) and you'll figure it out =IF(MONTH(($A$1-WEEKDAY($A$1,1))+(ROWS(A$1:$A1)*7)-1)<MONTH($A$1),(($A$1+7-WEEKDAY($A$1+7,1))+(ROWS(A$1:$A1)*7)-1),($A$1-WEEKDAY($A$1,1))+(ROWS(A$1:$A1)*7)-1) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "MasterDragon" wrote: MY question is simple, sorry if i'm being stupid. Can anyone tell me how can i make a list which contains only dates of all the Fridays in a particular month? In a more advanced sense, can I create a list with all the Fridays AND Saturdays of a month? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to list dates in a month of particular weekday
Here is a much shorter function to do what the function at the link does...
Function NthWeekday(DateIn As Date, Nth As Long, DOW As Long) As Date NthWeekday = DateSerial(Year(DateIn), Month(DateIn), 1 + 7 * Nth) - _ Weekday(DateSerial(Year(DateIn), Month(DateIn), 8 - DOW)) End Function This function is just the general formula I posted elsewhere in this thread converted to a one-liner VB statement. -- Rick (MVP - Excel) "Jeff" wrote in message ... MSDN has a good exaple of finding a day http://msdn.microsoft.com/en-us/libr...32(VS.60).aspx "MasterDragon" wrote: MY question is simple, sorry if i'm being stupid. Can anyone tell me how can i make a list which contains only dates of all the Fridays in a particular month? In a more advanced sense, can I create a list with all the Fridays AND Saturdays of a month? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to list dates in a month of particular weekday
On Sat, 23 Jan 2010 08:28:01 -0800, MasterDragon
wrote: MY question is simple, sorry if i'm being stupid. Can anyone tell me how can i make a list which contains only dates of all the Fridays in a particular month? In a more advanced sense, can I create a list with all the Fridays AND Saturdays of a month? With some date in the month of interest in A1 First Friday of the month: =A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2) Or, to generalize: =A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+8-DOW) Where DOW = Day of Week (Sun = 1) So the first Saturday would be: =A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+1) So, to get all the Fridays in a given month, again with some date in that month in A1: B1: =IF(MONTH($A$1-DAY($A$1)+8-WEEKDAY($A$1-DAY($A$1)+2)+ (ROWS($1:1)-1)*7)<MONTH($A$1),"",$A$1-DAY($A$1)+8- WEEKDAY($A$1-DAY($A$1)+2)+(ROWS($1:1)-1)*7) This tests to make sure the month output is the same as the month of A1. The same, obviously, would apply for Saturdays. --ron |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to list dates in a month of particular weekday
On Sat, 23 Jan 2010 16:41:04 -0500, Ron Rosenfeld
wrote: B1: =IF(MONTH($A$1-DAY($A$1)+8-WEEKDAY($A$1-DAY($A$1)+2)+ (ROWS($1:1)-1)*7)<MONTH($A$1),"",$A$1-DAY($A$1)+8- WEEKDAY($A$1-DAY($A$1)+2)+(ROWS($1:1)-1)*7) This tests to make sure the month output is the same as the month of A1. The same, obviously, would apply for Saturdays. Forgot to state to "fill down" at least five rows. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List of dates for month from start date | Excel Discussion (Misc queries) | |||
Counting Blanks in a list of dates by month | Excel Worksheet Functions | |||
LIST DATES IN MONTH ORDER | Excel Worksheet Functions | |||
Xth Weekday of the Month/Year | Excel Programming | |||
How do I sort a list of dates in Excel by month and day? | Excel Discussion (Misc queries) |