Solved

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

Posted on 2011-03-03
4
238 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:garyrobbins
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 14

Expert Comment

by:theras2000
ID: 35031216
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 35031232
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 35031261
...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
 

Author Closing Comment

by:garyrobbins
ID: 35036679
Barry, you are a genie!!

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

Gary
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question