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


.

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


.