date from sheet name

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

I'm going to first build this up piece by piece then put it all into one huge
ugly formula. Key here is that the workbook must have already been saved
before the CELL() function will work, until then, you get nothing out of it.

For the examples, we'll assume that you have already saved the workbook, and
have cells A1, A2, A3 and A4 available to test with.
this gives us the full path to the referenced cell, including the sheet name.
in A1: =CELL("filename",A1)
now we just grab the 1st 3 characters, which we assume are the abbreviation
for a month, as "Jan", "Feb", etc.
in A2: =LEFT(RIGHT(A1,LEN(A1)-FIND("]",A1)),3)
next we find out which month number the month is
in A3:
=MATCH(LEFT(RIGHT(A1,LEN(A1)-FIND("]",A1)),3),{"Jan","Feb","Mar","Apr","May","Jun","Ju l","Aug","Sep","Oct","Nov","Dec"},0)

Finally we can turn that into this formula:
=DATE(YEAR(NOW()),A3,1)
But we've taken several cells to arrive at the result, so we can then begin
combining them up to end up with this formula, which could be entered into
any cell on any given worksheet to get the date from the sheet's name:

=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)


"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

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

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

Hang on, another slap to my forehead - didn't use the date, I'll have to
modify the monster a bit, and that'll take a few minutes. Don't worry, it'll
only get a "little" longer - of course, little is relative g


"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

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

Another possibility. This one relies on sheet names always having two
digits for the day(s) of the month, and the month always being 3 letters.
e.g. Jan01, Jan01-02

=IF(LEFT(RIGHT(CELL("filename"),3),1)="-",RIGHT(CELL("filename"),8),
DATEVALUE(RIGHT(CELL("filename"),2)&" "&
LEFT(RIGHT(CELL("filename"),5),3)&" "&YEAR(TODAY())))

I'll play around with it a bit to allow for single digit days.



"JBoulton" wrote in message
...
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

Here we go (another monster):

=IF((LEN(CELL("filename"))-FIND("]",CELL("filename")))5,
RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",
CELL("filename"))),DATEVALUE(RIGHT(CELL("filename" ),
LEN(CELL("filename"))-FIND("]",CELL("filename"))-3)&" "&
LEFT(RIGHT(CELL("filename"),LEN(CELL("filename"))-
FIND("]",CELL("filename"))),3)&" "&YEAR(TODAY())))

Month name must always be 3 letters, but day numbers can be 1 or 2 digits.


HTH
Steve D.


"Steve Dunn" wrote in message
...
Another possibility. This one relies on sheet names always having two
digits for the day(s) of the month, and the month always being 3 letters.
e.g. Jan01, Jan01-02

=IF(LEFT(RIGHT(CELL("filename"),3),1)="-",RIGHT(CELL("filename"),8),
DATEVALUE(RIGHT(CELL("filename"),2)&" "&
LEFT(RIGHT(CELL("filename"),5),3)&" "&YEAR(TODAY())))

I'll play around with it a bit to allow for single digit days.



"JBoulton" wrote in message
...
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

Yes! Really nice.

"Steve Dunn" wrote:

Here we go (another monster):

=IF((LEN(CELL("filename"))-FIND("]",CELL("filename")))5,
RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",
CELL("filename"))),DATEVALUE(RIGHT(CELL("filename" ),
LEN(CELL("filename"))-FIND("]",CELL("filename"))-3)&" "&
LEFT(RIGHT(CELL("filename"),LEN(CELL("filename"))-
FIND("]",CELL("filename"))),3)&" "&YEAR(TODAY())))

Month name must always be 3 letters, but day numbers can be 1 or 2 digits.


HTH
Steve D.


"Steve Dunn" wrote in message
...
Another possibility. This one relies on sheet names always having two
digits for the day(s) of the month, and the month always being 3 letters.
e.g. Jan01, Jan01-02

=IF(LEFT(RIGHT(CELL("filename"),3),1)="-",RIGHT(CELL("filename"),8),
DATEVALUE(RIGHT(CELL("filename"),2)&" "&
LEFT(RIGHT(CELL("filename"),5),3)&" "&YEAR(TODAY())))

I'll play around with it a bit to allow for single digit days.



"JBoulton" wrote in message
...
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

Slight amendment, CELL("filename") refers to the active sheet, which isn't
always desirable. Use CELL("filename",A1) instead



"Steve Dunn" wrote in message
...
Here we go (another monster):

=IF((LEN(CELL("filename"))-FIND("]",CELL("filename")))5,
RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",
CELL("filename"))),DATEVALUE(RIGHT(CELL("filename" ),
LEN(CELL("filename"))-FIND("]",CELL("filename"))-3)&" "&
LEFT(RIGHT(CELL("filename"),LEN(CELL("filename"))-
FIND("]",CELL("filename"))),3)&" "&YEAR(TODAY())))

Month name must always be 3 letters, but day numbers can be 1 or 2 digits.


HTH
Steve D.


"Steve Dunn" wrote in message
...
Another possibility. This one relies on sheet names always having two
digits for the day(s) of the month, and the month always being 3 letters.
e.g. Jan01, Jan01-02

=IF(LEFT(RIGHT(CELL("filename"),3),1)="-",RIGHT(CELL("filename"),8),
DATEVALUE(RIGHT(CELL("filename"),2)&" "&
LEFT(RIGHT(CELL("filename"),5),3)&" "&YEAR(TODAY())))

I'll play around with it a bit to allow for single digit days.



"JBoulton" wrote in message
...
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