once again I am here for help, I've read hundreds of tutorials on how to fill dates, get every 1st and 3rd day, every Monday ect ect.. but none of them seem to account for the 28 or 29 days in February..
In my sheets, the month is pulled from the sheet name, then converted to a number, the year is referenced from another sheet, and with some fancy (but likely clumsily formulated) math I am able to fill cells with the number of each Tuesday in the month. My problem now, is February does not have more than 28 days (29 on leapyear) and this is where my issue is. There must be an easier way to do this, I have a feeling I overcomplicated things as this sheet evolved over the years.
Is there a more elegant modification or function that can account for less than 30 days for Feb?
My alternative "solution" (if it can be called one) is to put in a clumsy IF check to see if the month is feb and modify the math a little to account for it.
Open to any suggestions or alternative solutions... maybe this can help someone else.
In my sheets, the month is pulled from the sheet name, then converted to a number, the year is referenced from another sheet, and with some fancy (but likely clumsily formulated) math I am able to fill cells with the number of each Tuesday in the month. My problem now, is February does not have more than 28 days (29 on leapyear) and this is where my issue is. There must be an easier way to do this, I have a feeling I overcomplicated things as this sheet evolved over the years.
Is there a more elegant modification or function that can account for less than 30 days for Feb?
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E6:E8 | E6 | =(DAY(DATE($B$5,$C6,8)-WEEKDAY(DATE($B$5,$C6,5)))) |
| F6:I8 | F6 | =@IFERROR(IF(E6,IF(E6>(31-7),B,SUM(E6+7)),x),"") |
| C6:C8 | C6 | =MONTH(DATEVALUE(B6&" 1, "&$B$5)) |
My alternative "solution" (if it can be called one) is to put in a clumsy IF check to see if the month is feb and modify the math a little to account for it.
| Book2.xlsx | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| K | L | M | N | O | P | Q | R | |||
| 4 | tuesdays as day with an IF | |||||||||
| 5 | 2021 | 1st | 2nd | 3rd | 4th | 5th | ||||
| 6 | jan | 1 | 5 | 12 | 19 | 26 | ||||
| 7 | feb | 2 | 2 | 9 | 16 | 23 | ||||
| 8 | mar | 3 | 2 | 9 | 16 | 23 | 30 | |||
JAN | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| N6:N8 | N6 | =(DAY(DATE($K$5,$L6,8)-WEEKDAY(DATE($K$5,$L6,5)))) |
| O6:R8 | O6 | =IF($L6=2,@IFERROR(IF(N6,IF(N6>(29-7),B,SUM(N6+7)),x),""),@IFERROR(IF(N6,IF(N6>(31-7),B,SUM(N6+7)),x),"")) |
| L6:L8 | L6 | =MONTH(DATEVALUE(K6&" 1, "&$K$5)) |
Open to any suggestions or alternative solutions... maybe this can help someone else.
