The problem I've always had with big, long, ugly looking formulas like this
one is in maintaining it later. Because there are so many dependencies built
into it, sometimes it's better to simply have a group of "helper" cells, each
doing a little piece of the job and then the composite formula referencing
those other formulas where you actually need the result. You can always put
the helpers out of site in hidden cells or even on a hidden sheet.
Regardless of the solution to a given problem, not just this one, a key is
being able to understand the formula and make changes to it if/when you need
to or modify it to work under slightly different situations.
At least 3 solutions have been proposed in this discussion to this problem,
I'd pick the one I understand the most, and probably put a little note
somewhere to yourself on just how it does work should you ever need to change
it, such as in a case where the sheet name also contained the year. The note
doesn't have to go into the workbook, it could be in a .txt file created with
Notepad and stuck into the same folder with the workbook for future reference.

"JBoulton" wrote:

That one's good, too.

Thanks for the lesson.

"JLatham" wrote:

Here, this monster of a monster should do it:

=IF(LEN(RIGHT(CELL("filename",A1),LEN(CELL("filena me",A1))-FIND("]",CELL("filename",A1))))<6,DATE(YEAR(NOW()),MATCH( LEFT(RIGHT(CELL("filename",A1),LEN(CELL("filename" ,A1))-FIND("]",CELL("filename",A1))),3),{"Jan","Feb","Mar","Apr ","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"} ,0),RIGHT(RIGHT(CELL("filename",A1),LEN(CELL("file name",A1))-FIND("]",CELL("filename",A1))),LEN(RIGHT(CELL("filename", A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))))-3)),
RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))))


"JBoulton" wrote:

That's some formula! With sheetname = Apr27 I get 4/1/2010 as the formula
result, though. Can you help me tweak it?

"JLatham" wrote:

I missed out on the "if it is Apr28-30 then return Apr28-30 as text" part.
This will cover it. First the 'short' formula:
=IF(LEN(RIGHT(CELL("filename",A1),LEN(CELL("filena me",A1))-FIND("]",CELL("filename",A1))))<6,
"use long formula" ,
RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))))

And then we substitute that big ugly formula I came up with earlier into
this one to come up with:

=IF(LEN(RIGHT(CELL("filename",A1),LEN(CELL("filena me",A1))-FIND("]",CELL("filename",A1))))<6,DATE(YEAR(NOW()),MATCH( LEFT(RIGHT(CELL("filename",A1),LEN(CELL("filename" ,A1))-FIND("]",CELL("filename",A1))),3),{"Jan","Feb","Mar","Apr ","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"} ,0),1),
RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))))

Format the cell as Date so you'll get a date when one is generated.
Remember that you'll have to make sure it's entered as one long line instead
of broken into numerous lines as it no doubt is now. I suggest copying it
into Notepad and then removing linefeeds and then copying it into a cell in
your workbook.

"JBoulton" wrote:

I'd like to turn a sheet name into a date.

Apr28 to 4/28/10 (as a date)
and
Apr28-30 to Apr28-30 (as text)

TIA and thanks for any ideas.

Jim