Forum Discussion

ECASUL's avatar
ECASUL
Copper Contributor
Jan 26, 2023
Solved

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 F...
excel
  • mathetes's avatar
    mathetes
    Jan 26, 2023
    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.
ECASUL's avatar
ECASUL
Copper Contributor
Jan 26, 2023
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.
mathetes's avatar
mathetes
Silver Contributor
Jan 30, 2023

ECASUL 

 

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.

  • Jacques_Maritz's avatar
    Jacques_Maritz
    Copper Contributor
    Jan 23, 2024

    mathetes can you assist with a formula to fill columns with dates for only Monday and Thursday every week.

    • mathetes's avatar
      mathetes
      Silver Contributor
      Jan 23, 2024

       

      Jacques_Maritz 

       

      I modified the earlier formula so it gives you the first Monday in any given month. From that starting point, it's easy to add 3 for the next Thursday, then 4 for the next Monday. And repeat.

Resources