date from sheet name

  • Thread starter Thread starter JBoulton
  • Start date Start date
J

JBoulton

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","Jul","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)
 
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("filename",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("filename",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.
 
That's some formula! With sheetname = Apr27 I get 4/1/2010 as the formula
result, though. Can you help me tweak it?

JLatham said:
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("filename",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("filename",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 said:
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
Click to expand...
Click to expand...
 
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 said:
That's some formula! With sheetname = Apr27 I get 4/1/2010 as the formula
result, though. Can you help me tweak it?

JLatham said:
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("filename",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("filename",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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Here, this monster of a monster should do it:

=IF(LEN(RIGHT(CELL("filename",A1),LEN(CELL("filename",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("filename",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 said:
That's some formula! With sheetname = Apr27 I get 4/1/2010 as the formula
result, though. Can you help me tweak it?

JLatham said:
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("filename",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("filename",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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.
 
With =CELL("filename") in a1 and your formula, I get "0.xlsApr28" as the
formula result. With a1 blank I get #VALUE.

It'll take me a while to figure out what you're trying to get at.

p45cal said:
You could try this monster:
=IF(LEN(TRIM(RIGHT(SUBSTITUTE(A1,"]",REPT("
",30)),10)))<6,DATEVALUE(MID(TRIM(RIGHT(SUBSTITUTE(CELL("filename",A1),"]",REPT("
",30)),10)),4,20) &
LEFT(TRIM(RIGHT(SUBSTITUTE(CELL("filename",A1),"]",REPT("
",30)),10)),3)),TRIM(RIGHT(SUBSTITUTE(A1,"]",REPT("
",30)),10)))
though I'd be worried it may not give the same results as I get here in
the UK.


That's some formula! With sheetname = Apr27 I get 4/1/2010 as the
formula
result, though. Can you help me tweak it?

JLatham said:
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("filename",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("filename",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.

:

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
Click to expand...
Click to expand...


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=199130

http://www.thecodecage.com/forumz

.
Click to expand...
 
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.
 
Yes! Really nice.

Steve Dunn said:
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 said:
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.
Click to expand...
Click to expand...
 
Slight amendment, CELL("filename") refers to the active sheet, which isn't
always desirable. Use CELL("filename",A1) instead



Steve Dunn said:
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 said:
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.
Click to expand...
Click to expand...
 
I figured that out. But it is a good solution.

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



Steve Dunn said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
That one's good, too.

Thanks for the lesson.

JLatham said:
Here, this monster of a monster should do it:

=IF(LEN(RIGHT(CELL("filename",A1),LEN(CELL("filename",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("filename",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 said:
That's some formula! With sheetname = Apr27 I get 4/1/2010 as the formula
result, though. Can you help me tweak it?

JLatham said:
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("filename",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("filename",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.

:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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.
 
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 said:
That one's good, too.

Thanks for the lesson.

JLatham said:
Here, this monster of a monster should do it:

=IF(LEN(RIGHT(CELL("filename",A1),LEN(CELL("filename",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("filename",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 said:
That's some formula! With sheetname = Apr27 I get 4/1/2010 as the formula
result, though. Can you help me tweak it?

:

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("filename",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("filename",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.

:

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
Click to expand...
Click to expand...
Click to expand...
 
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","jul","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 said:
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 said:
That one's good, too.

Thanks for the lesson.

JLatham said:
Here, this monster of a monster should do it:

=IF(LEN(RIGHT(CELL("filename",A1),LEN(CELL("filename",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("filename",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))))


:

That's some formula! With sheetname = Apr27 I get 4/1/2010 as the formula
result, though. Can you help me tweak it?

:

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("filename",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("filename",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.

:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
That one's good, too.

Thanks for the lesson.

JLatham said:
Here, this monster of a monster should do it:

=IF(LEN(RIGHT(CELL("filename",A1),LEN(CELL("filename",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("filename",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))))


:

That's some formula! With sheetname = Apr27 I get 4/1/2010 as the formula
result, though. Can you help me tweak it?

:

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("filename",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("filename",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.

:

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
Click to expand...
Click to expand...
Click to expand...
 
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.


T. Valko said:
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


JBoulton said:
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
Click to expand...


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

T. Valko said:
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


JBoulton said:
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
Click to expand...


.
Click to expand...
 
Back
Top