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

Posted on 2011-03-03
Medium Priority
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:

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?

Question by:garyrobbins
  • 2
LVL 14

Expert Comment

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.
You could also merge your previous formula into it to make one huge formula, but that'd get pretty messy.
LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 35031232
Try using your original formula with +0 at the end, that will convert the text to a date


format as required

regards, barry
LVL 50

Expert Comment

by:barry houdini
ID: 35031261
...another way would be to use DATEVALUE function....but you get the same result



Author Closing Comment

ID: 35036679
Barry, you are a genie!!

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


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…

624 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