Using formulas to automate a date

Autumn Montegna

Hello! I have a column that identifies a specific week, e.g., week 1, week 2, etc. I would like to create another column that automates a specific date based on week entry. For instance if Week 1 is entered, the date 1/25/21 is automatically entered in the date column. Thank you for your help!

Mark Cronk

Hi @Autumn Montegna ,

I assumed your week number was in column [week] and entered as text, e.g. Week 1. The formula is simpler if your week number is just a number. I also assumed all dates are 2021. Try:

=MAX(DATE(2021,1,1),DATE(2021,1,1)-WEEKDAY(DATE(2021,1,1))+(VALUE(RIGHT([week]@row, LEN([week]@row)-FIND(" ",[week]@row)))-1)*7+9)

It should return the Monday date of the week number.

Mark