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)


"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

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" 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

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("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" 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

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("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" 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

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" 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("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" 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

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" <(E-Mail Removed)> wrote in message
news:B4F35F16-412C-4E75-B49E-(E-Mail Removed)...
> 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

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" wrote:

>
> 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.
>
>
>
JBoulton;711544 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("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" 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
>
>
> --
> p45cal
>
> *p45cal*
> ------------------------------------------------------------------------
> p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
> View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=199130
>
> http://www.thecodecage.com/forumz
>
> .
>

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" <(E-Mail Removed)> wrote in message
news:B9DC9642-A036-4432-951A-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:B4F35F16-412C-4E75-B49E-(E-Mail Removed)...
>> 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! 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" <(E-Mail Removed)> wrote in message
> news:B9DC9642-A036-4432-951A-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:B4F35F16-412C-4E75-B49E-(E-Mail Removed)...
> >> 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
> >
>