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

date from sheet name
 

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

date from sheet name
 

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

date from sheet name
 

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

date from sheet name
 

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

date from sheet name
 

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

date from sheet name
 

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

date from sheet name
 

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

date from sheet name
 

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

date from sheet name
 

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

date from sheet name
 

I figured that out. But it is a good solution.

"Steve Dunn" wrote:

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

date from sheet name
 

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

date from sheet name
 

Had a Doh! moment, just before bed...

=IF((LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))5,
MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,8),
DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+4,2)&
MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3)))

much neater, same result.

Goodnight.



"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

date from sheet name
 

Can I join in the fun?

Create this defined name...

Goto to the menu InsertNameDefine
Name: SheetName
Refers to:

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

OK out

Then:

=IF(COUNT(FIND("-",SheetName)),SheetName,--TEXT(SheetName,"mmm dd"))

Format as Date.

This works on my machine with U.S. English regional date settings. Some
international locations use different codes for date formatting. For
example, in the U.S (and many other locations) we use "mmm" for the short
month name and "dd" for the 2 digit day. Other locations may use different
codes and Excel doesn't automatically change the code for the location.

--
Biff
Microsoft Excel MVP


"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

date from sheet name
 

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

date from sheet name
 

To follow up on my last post. Here are 2 series of formulas that could be
used and more easily modified to deal with different date entries in sheet
tabs:

#1 for your original situation where you have 3-letter month followed either
by a single date or range of dates:
F1 basic information: =CELL("filename",F1)
F2 sheet name only: =RIGHT(F1,LEN(F1)-FIND("]",F1))
F3 Month as 3-letter: =LEFT(F2,3)
F4 Month as number:
=MATCH(F3,{"jan","feb","mar","apr","may","jun","ju l","aug","sep","oct","nov","dec"},0)
F5 Day(s) portion: =RIGHT(F2,LEN(F2)-3)
F6 The year: =YEAR(NOW())
and then the formula you can put anywhere
=IF(LEN(F5)2,F2,DATE(F6,F4,F5))

And this can be modified to use a sheet name that contains a 4-digit year,
like
Apr-2-2010 or with a range in it like Apr-2-23-2010
F1, F2, F3 and F4 remain unchanged
F5 day or days(): =MID(G2,5,LEN(G2)-9)
in that one change 9 to 7 if using a 2-digit year.
F6 the year: =RIGHT(G2,4)
change ,4 to ,2 if using a 2-digit year.
And the final formula remains unchanged!

The F1:F6 cells could be out of site in hidden columns or on some other
sheet entirely, just change the final formula to reference that other sheet,
as:
=IF(LEN('Helper Sheet'!F5)2,'Helper Sheet'!F2,DATE('Helper Sheet'!F6,
'Helper Sheet'!F4, 'Helper Sheet'!F5))


"JLatham" wrote:

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

date from sheet name
 

Oops, if you do put those on another sheet, your inital (F1) formula needs to
specifically reference a cell on the sheet with the tab name you need to
evaluate, as:
=CELL("address",'Apr-2-6-2010'!A1)

"JLatham" wrote:

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

date from sheet name
 

Wow! That's an even better solution. It's simple and straight forward.

I can't utilize the defined name approach due to the users, but I can use
the approach as modified he

=IF(COUNT(FIND("-",MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))),MID(CELL("filename ",A1),FIND("]",CELL("filename",A1))+1,255),--TEXT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),"mmm dd"))

which acheives the same result.

Thanks for joining! It was a fun project.


"T. Valko" wrote:

Can I join in the fun?

Create this defined name...

Goto to the menu InsertNameDefine
Name: SheetName
Refers to:

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

OK out

Then:

=IF(COUNT(FIND("-",SheetName)),SheetName,--TEXT(SheetName,"mmm dd"))

Format as Date.

This works on my machine with U.S. English regional date settings. Some
international locations use different codes for date formatting. For
example, in the U.S (and many other locations) we use "mmm" for the short
month name and "dd" for the 2 digit day. Other locations may use different
codes and Excel doesn't automatically change the code for the location.

--
Biff
Microsoft Excel MVP


"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


.

date from sheet name
 

One question, though. How does that get the year from Apr29 formatted as mmm
dd?

"T. Valko" wrote:

Can I join in the fun?

Create this defined name...

Goto to the menu InsertNameDefine
Name: SheetName
Refers to:

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

OK out

Then:

=IF(COUNT(FIND("-",SheetName)),SheetName,--TEXT(SheetName,"mmm dd"))

Format as Date.

This works on my machine with U.S. English regional date settings. Some
international locations use different codes for date formatting. For
example, in the U.S (and many other locations) we use "mmm" for the short
month name and "dd" for the 2 digit day. Other locations may use different
codes and Excel doesn't automatically change the code for the location.

--
Biff
Microsoft Excel MVP


"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


.

date from sheet name
 

Yes. Very nice and much neater.

"Steve Dunn" wrote:

Had a Doh! moment, just before bed...

=IF((LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))5,
MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,8),
DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+4,2)&
MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3)))

much neater, same result.

Goodnight.



"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

date from sheet name
 

How does that get the year from Apr29 formatted as mmm dd?

It defaults to the current year.

--
Biff
Microsoft Excel MVP


"JBoulton" wrote in message
...
One question, though. How does that get the year from Apr29 formatted as
mmm
dd?

"T. Valko" wrote:

Can I join in the fun?

Create this defined name...

Goto to the menu InsertNameDefine
Name: SheetName
Refers to:

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

OK out

Then:

=IF(COUNT(FIND("-",SheetName)),SheetName,--TEXT(SheetName,"mmm dd"))

Format as Date.

This works on my machine with U.S. English regional date settings. Some
international locations use different codes for date formatting. For
example, in the U.S (and many other locations) we use "mmm" for the short
month name and "dd" for the 2 digit day. Other locations may use
different
codes and Excel doesn't automatically change the code for the location.

--
Biff
Microsoft Excel MVP


"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


.

date from sheet name
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"JBoulton" wrote in message
...
Wow! That's an even better solution. It's simple and straight forward.

I can't utilize the defined name approach due to the users, but I can use
the approach as modified he

=IF(COUNT(FIND("-",MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))),MID(CELL("filename ",A1),FIND("]",CELL("filename",A1))+1,255),--TEXT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),"mmm
dd"))

which acheives the same result.

Thanks for joining! It was a fun project.


"T. Valko" wrote:

Can I join in the fun?

Create this defined name...

Goto to the menu InsertNameDefine
Name: SheetName
Refers to:

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

OK out

Then:

=IF(COUNT(FIND("-",SheetName)),SheetName,--TEXT(SheetName,"mmm dd"))

Format as Date.

This works on my machine with U.S. English regional date settings. Some
international locations use different codes for date formatting. For
example, in the U.S (and many other locations) we use "mmm" for the short
month name and "dd" for the 2 digit day. Other locations may use
different
codes and Excel doesn't automatically change the code for the location.

--
Biff
Microsoft Excel MVP


"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


.