I've a question, How to List dates of all Fridays in a given month? Some months have 5 Fridays and some have 4 Fridays in month.
Month of May-2020 e.g. A1=01/May/2020 the result should be in B1:B5 as 01/May, 08/May, 15/May, 22/May, 29/May
Thank you for taking you time to help me out.
14 Answers
For the first Friday, use
=CEILING(EOMONTH(A1,-1)-5,7)+6for the next three, add 7 to the date above. For the fifth, check if the date above + seven is still in the desired month. If yes, add the 7, if not, return a blank
In B1, enter below formula to calculate the first Friday of the month. It calculates by finding the first Friday taking into account the day before the one entered on A1. In this case, the first Friday is found compared to 30th April.
=WORKDAY.INTL(A1-1,1,"1111011")In B2, enter this formula to calculate the other Fridays. They are found by considering the the day that comes after on the previous cells. So if 01 May is Friday, the next Friday is calculated from the 2nd. A comparison is also done to check if the months from both cells are the same. If yes, the date is shown. If not, an empty value will be displayed.
=IF(MONTH(WORKDAY.INTL(B1+1,1,"1111011"))=MONTH(B1), WORKDAY.INTL(B1+1,1,"1111011"),"")Drag the formula in B2 down.
I would like to suggest the most fastest method is VBA Macro, is straight forward no adjustments & time saver too.
Sub AllFriday()
getmonth = InputBox("Enter month Like This,,", "Enter Month", "1/1/20") While WorksheetFunction.Weekday(DateValue(getmonth)) <> 6 getmonth = DateValue(getmonth) + 1 Wend Range("A51") = DateValue(getmonth) cnta = 1 While Month(DateValue(getmonth) + 7) = Month(getmonth) Range("A51").Offset(cnta, 0) = DateValue(getmonth) + 7 cnta = cnta + 1 getmonth = DateValue(getmonth) + 7 Wend
End SubN.B.
- Press Alt + F11 to get VB editor.
- Copy & Paste this code as Worksheet Module (standard module).
- Press Alt+Q to return to the Sheet.
- Save the Workbook as Macro Enabled
*.xlsm. - RUN the Macro.
- This code will display all Fridays in Row (A51:A54).
If want Fridays in Column (A51:D51) then use this,,
Range("a51").Offset(0, cnta) = DateValue(getmonth) + 7- Output Range
A51is editable. - Date format for INPUT BOX is
MM/DD/YY. - Apply cell format
dddd, dd-mmm, yyyyonA51:A54.
This can also be done with SEQUENCE and FILTER.
Basically, from the sequence of dates between the start and the end of the selected month, filter just the dates whose weekday is 6 (default is that Sunday is 1):
To simplify the formula, I created some helper cells (also named) for:
startdate:
=DATE(2020,mth,1)enddate (named eomonth):
=EOMONTH(startdate,0)days:
=eomonth-startdate+1And finally the formula to return the dates:
=FILTER(SEQUENCE(days,1,startdate,1),WEEKDAY(SEQUENCE(days,1,startdate,1))=6)It shouldn't be too hard to see that you can parameterize which day you are selecting (so instead of 6 at the end of the FILTER function, you could have a reference to a cell, or a lookup such that a user can select the day they want the dates for).
Similarly, to avoid the use of helper cells, you can replace each cell name in the FILTER function (i.e. days and startdate) with the appropriate formulas shown above.
This will all be much simpler when the LET function is widely available. You can read more about that here.
