How to extract a WorkSheet Name and have it then formatted as a date field.

Hello again Experts, I need your help.

I want to extract a WorkSheet Name (eg. 02-24-2011) into a cell and have it then formatted as a date field (eg. mm/dd/yyyy).

I use this formula in the cell to extract the WorkSheet Name:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

However, even though I format the cell to be (mm/dd/yyyy), the result is still 02-24-2011 and not recognized as a date.

Is there anything I can do to work around this?

Gary
garyrobbinsAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
Try using your original formula with +0 at the end, that will convert the text to a date

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)+0

format as required

regards, barry
0
 
theras2000Commented:
You can't just tell text to be a date.  You have to use the DATE() function, and feed it the 3 individual numbers - year, month, day - then Excel will see it as a proper date.
Assuming that your previous formula is in B1, you could use this formula in another cell.
=DATE(RIGHT(B1,4),LEFT(B1,2),MID(B1,4,2))
You could also merge your previous formula into it to make one huge formula, but that'd get pretty messy.
0
 
barry houdiniCommented:
...another way would be to use DATEVALUE function....but you get the same result

=DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))

barry
0
 
garyrobbinsAuthor Commented:
Barry, you are a genie!!

Thank you, again, for your fast and engenious response.

Gary
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.