Forum Discussion
AUTOFILL SPECIFIC DAY OF WEEK DATE IN CELL
I want to put the first day of the month in a cell and then put in a formula that will recognize and put the date of one day of the week in another cell. Please look at the spreadsheet picture for February 2023 as an example of what I want.
- Your examples are always three days apart, with you entering a Wednesday date and the system generating the Saturday date. If that's all you're doing then the formula in cell I3 would be as simple as =D3+3 and then just format the cell as a short date.
If that's not the answer you're seeking, please come back and explain more fully; it would also be helpful if you could describe briefly the whole purpose of the workbook.
- Your examples are always three days apart, with you entering a Wednesday date and the system generating the Saturday date. If that's all you're doing then the formula in cell I3 would be as simple as =D3+3 and then just format the cell as a short date.
If that's not the answer you're seeking, please come back and explain more fully; it would also be helpful if you could describe briefly the whole purpose of the workbook.- Thank you for the help and response. If the first of the month is a Monday the formula will look for and place the Wednesday and Saturday dates in the appropriate cell. The month of February has Feb 1 falls on a Wednesday so I would want the formula to put that date in the D3 cell; In March the first falls on a Wednesday as well; Please look at April. The first falls on a Saturday So I would need the D3 cell to have the first Wednesday Date of 04/05/2023 and the first Saturday date of 04/01/2023 to be put in Cell I3. D9 would be 04/12/23, D15 04/19/23, D21 04/26/23 which is each Wednesday in April, and I3, I9, I15, and I21 would have each Saturday date of April.
Please don't give up on me. Thank you again for your help and expertise.Here's a formula that will return the date of the first Wednesday of any month in any year.
=LET(FstDa,DATE(CurrYr,CurrMo,1),FstDa+CHOOSE(WEEKDAY(FstDa),3,2,1,0,6,5,4))
That formula requires a relatively new version of Excel (in order for the LET function to work).
If you don't have a new enough version of Excel, this formula will work:
=DATE(CurrYr,CurrMo,1)+CHOOSE(WEEKDAY(DATE(CurrYr,CurrMo,1)),3,2,1,0,6,5,4)
In those formulas, you can substitute cell references containing the current year and current month for the text reading "CurrYr" and "CurrMo" or -- preferable -- use named ranges.
I've attached a spreadsheet with both of these formulas working. I'll leave it up to you to complete the rest of your spreadsheet.
