Sheet tab and date

Occasional Visitor



I search a way to put a date on a cell base on the number of the sheet tab. 


ex: if the sheet tab is "2" I want that excel put in the "A7" cell the date "02 january 2022" and etc for all the 31 days of a month. Sheet tab "3" for the 3 january 2022


is it possible ? 


thank you 

1 Reply


It depends on you Excel version/platform and what you'd like to do with that data (convert to structured table, sort, filter, etc).


As variant in A7 could be

=DATE( 2022, 1, MID( CELL("filename", A7), SEARCH( "]", CELL("filename", A7) )+1, 2) )

in A8

=IF( A7="", "", IF( MONTH(A7+1) = MONTH(A7), A7+1, "") )

and drag it down till empty cells appear.