I am looking to populate a date list with either the Friday of each successive week or the last Friday of Month. Example is attached.
The period is either Weekly or Monthly and is in cell J4. Cell H6 is the date above Week 1 and is input. The If statement formula for a Week selection is above Period 2 is noted below the table and starts in I6, Period 3 is J6 etc.
Period Weekly
1/30 2/6 2/13 2/20
1 2 3 4
This works for each successive Friday.
=IF($J4="Weekly",H6+7,DATE(YEAR(A1),MONTH(A1)+1,1)- WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,2)))
On a separate tab for experimental purposes, I have the following formula that works for the last Friday of each month. The table below, with dates in Col A shows the correct last Friday for that month in Col C.
=DATE(YEAR(A8),MONTH(A8)+1,0)+MOD(-WEEKDAY(DATE(YEAR(A8),MONTH(A8)+1,0),2)-2,-7)
2/27/15 2/27/2015
2/28/15 2/27/2015
3/1/15 3/27/2015
3/2/15 3/27/2015
This is what should be the result for a Monthly selection.
Period Monthly
1/30 2/27 3/27 4/24
1 2 3 4
How do I combine these so that given Weekly or Monthly selection that the dates are populated correctly for either choice? Use of a function would also be fine.
Bookmarks