Sheet tab name in cell converted to date

[sinmike]

Hi Forum Members,

I need help with the following situation:

I have a worksheet with 31 sheets and the sheet tabs are named in the format M.D.YY for each day of the month. For example todays tab would read 4.25.13 (btw. the format has to stay this way and should not be changed).

I need to copy the sheet tab name to a cell in the same sheet. I know how to do this with the following formula:
=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

However, I need the cell the show the date as Thursday, April 25, 2013 (Thursday 4/25/13 or Thursday 4.25.13 would also work) and not just as 4.25.13 that the above formula generates. A simple cell format change did not work.

Any help is greatly appreciated.

Thank you.

[JosephP]

perhaps
=TEXT(SUBSTITUTE(MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255),".","/")+0,"dddd, mmmm dd, yyyy")

or if you need it stored as a date use
=SUBSTITUTE(MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255),".","/")+0
and change the cell format

[Richard Buttrey]

Hi,

That's because the function returns text and not a number. You need to string slice the result of that formula and deconstruct it into three numbers for year, month and day, then rebuild a proper date number with the =DATE() function. Then format that as necessary.

Regards

[sinmike]

Great. Thank you very much for your help Joseph.