How to get dates based on a month

Hi, I am creating a workbook to help me keep track of the hours I have worked, so I want to create a document with 12 sheets 1each month.

Is there a formula that can write the dates for me in the ?Date? column based on the ?Month? and ?Year? i input in the relative cells?

F4612338-1E60-4EAF-AD00-ED9C0B4F4BD1.jpeg

Thanks in advance

How about in C6 only

Formula:

=LET(d,DATE(E3,MONTH("1/"&E2),1),SEQUENCE(DAY(EOMONTH(d,0)),,d))

clear all cells below C6 first

Originally Posted by Fluff13

How about in C6 only

Formula:

=LET(d,DATE(E3,MONTH("1/"&E2),1),SEQUENCE(DAY(EOMONTH(d,0)),,d))

clear all cells below C6 first

I get THE FIRST ARGUMENT OF LET MUST BE A VALID NAME

Also does this formula take in consideration the Year?

Originally Posted by AliGW

Clear your column of dates, then try this in C6:

=LET(sd,DATEVALUE(E2&" 1"),SEQUENCE(DAY(EOMONTH(sd,0)),1,sd,1))

This does change the dates (day and month) but the year is 2001, i would like the formula to keep in consideration what year do i write in the year cell so in E3

If you want a solution that will work in versions of Excel that do not have the LET or SEQUENCE functions, you can do this:

First of all, ensure that you spell the month name correctly in cell E2, or set this up with data validation so you can choose it from a list.

Then you can use this formula in cell C6:

=IFERROR(DATE(E3,INT((SEARCH(LEFT(E2,3),"JanFebmarAprMayJunJulAugSepOctNovDec")-1)/3)+1,1),"")

Note that you only have to enter the first 3 letters of the month in E2 for this to work. Then use this formula in C7:

=IF(C6="","",IF(MONTH(C6+1)<>MONTH(C6),"",C6+1))

Copy this down to C36 (i.e. for a full 31 days), and this will automatically adjust for months of 28, 29 or 30 days.

Hope this helps.

Pete

Originally Posted by AliGW

Amendment:

=LET(d,DATEVALUE(E2&" 1"),sd,DATE(E3,MONTH(d),DAY(d)),SEQUENCE(DAY(EOMONTH(sd,0)),1,sd,1))

Thanks a lot this works, I have done my own adjustments and changed with cell E3 where you typed 2022 so now the sheet work as i want..
thank you so much

I get THE FIRST ARGUMENT OF LET MUST BE A VALID NAME

Works for me