date from sheet name

[JBoulton]

Yes. Very nice and much neater.

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.

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.

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.



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

 

[T. Valko]

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

It defaults to the current year.

--
Biff
Microsoft Excel MVP

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

Can I join in the fun?

Create this defined name...

Goto to the menu Insert>Name>Define
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

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

.

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

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 here:

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

Can I join in the fun?

Create this defined name...

Goto to the menu Insert>Name>Define
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

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

.