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

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

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

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

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

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

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

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


.

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


.

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