Spreadsheet date [Closed]

Report
-
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
-
Hello,

Urban legend around my office says that you can put a formula into Excel that will change the date in a cell within the spreadsheet if you change the name of the spreadsheet on the tab.

For example if you name the tab, 101209, it will populate a cell in the spreadsheet with that date.

Any ideas?

Thanks,

Sabajeff

1 reply

Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Hello there

Use =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256) to get the sheet name to a cell
then use =DATE(RIGHT(N20,2)+1900,MID(N20,3,2),LEFT(N20,2)) to get the date where N2 is the cell where you have applied the above formula
To get everything in one formula replace N2 with the first formula (excluding'=')