Greetings,
I have a lot of workbooks with a date as a name (eg. 2004-08.xls or
1999-03.xls). I would like to capture the date portion of this name
with a formula in sheet 'Date' cell 'A4'.
Anyone have any ideas?
Your help is appreciated.
TIA
-Minitman
Use this formula in any cell of your workbook. It will output the name of
the workbook you place it in. So, for example, if you place this in
2004-08.xls, it will output "2004-08":
=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH(".xls",CELL("filename"))-SEARCH("[",CELL("filename"))-1)
--
Regards,
Dave
"Minitman" wrote:
> Greetings,
>
> I have a lot of workbooks with a date as a name (eg. 2004-08.xls or
> 1999-03.xls). I would like to capture the date portion of this name
> with a formula in sheet 'Date' cell 'A4'.
>
> Anyone have any ideas?
>
> Your help is appreciated.
>
> TIA
>
> -Minitman
>
=MID(CELL("filename"),1+FIND("[",CELL("filename")),FIND(".",CELL("filename"),FIND("[",CELL("filename")))-FIND("[",CELL("filename"))-1)
the file must be saved at least once before this formula will work.
"Minitman" wrote:
> Greetings,
>
> I have a lot of workbooks with a date as a name (eg. 2004-08.xls or
> 1999-03.xls). I would like to capture the date portion of this name
> with a formula in sheet 'Date' cell 'A4'.
>
> Anyone have any ideas?
>
> Your help is appreciated.
>
> TIA
>
> -Minitman
>
Hey David and Sloth,
Thanks guys. They both indeed return the text string for the file
name as you said. This maybe what I requested, it is, unfortunately,
not seen as a date. Is there anyway to convert this string into a
fully functual date?
TIA
-Minitman
On Tue, 6 Dec 2005 11:56:02 -0800, "Sloth"
<Sloth@discussions.microsoft.com> wrote:
>=MID(CELL("filename"),1+FIND("[",CELL("filename")),FIND(".",CELL("filename"),FIND("[",CELL("filename")))-FIND("[",CELL("filename"))-1)
>
>the file must be saved at least once before this formula will work.
>
>"Minitman" wrote:
>
>> Greetings,
>>
>> I have a lot of workbooks with a date as a name (eg. 2004-08.xls or
>> 1999-03.xls). I would like to capture the date portion of this name
>> with a formula in sheet 'Date' cell 'A4'.
>>
>> Anyone have any ideas?
>>
>> Your help is appreciated.
>>
>> TIA
>>
>> -Minitman
>>
Just a warning...
Change each of the:
CELL("filename")
to
CELL("filename",A1)
Else you'll get the filename of the activeworkbook when excel calculated.
Minitman wrote:
>
> Greetings,
>
> I have a lot of workbooks with a date as a name (eg. 2004-08.xls or
> 1999-03.xls). I would like to capture the date portion of this name
> with a formula in sheet 'Date' cell 'A4'.
>
> Anyone have any ideas?
>
> Your help is appreciated.
>
> TIA
>
> -Minitman
--
Dave Peterson
I'd use this.
=DATE(MID(CELL("filename"),FIND("-",CELL("filename"))-4,4),MID(CELL("filename"),FIND("-",CELL("filename"))+1,2),1)
The final "1" in the formula indicates day one. You can format the
result to show the date in any way desired.
- John
www.JohnMichl.com
Mintman,
Try to wrap the formula they sent you in the DATEVALUE() function:
=DATEVALUE(MID(CELL("filename",A1),1+FIND("[",CELL("filename",A1)),FIND(".",CELL("filename",A1),FIND("[",CELL("filename",A1)))-FIND("[",CELL("filename",A1))-1))
HTH,
Conan
"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
news:ogvbp1dvrpegi2ev9qhjro27vpfha1hqvn@4ax.com...
> Hey David and Sloth,
>
> Thanks guys. They both indeed return the text string for the file
> name as you said. This maybe what I requested, it is,
> unfortunately,
> not seen as a date. Is there anyway to convert this string into a
> fully functual date?
>
> TIA
>
> -Minitman
>
>
>
> On Tue, 6 Dec 2005 11:56:02 -0800, "Sloth"
> <Sloth@discussions.microsoft.com> wrote:
>
>>=MID(CELL("filename"),1+FIND("[",CELL("filename")),FIND(".",CELL("filename"),FIND("[",CELL("filename")))-FIND("[",CELL("filename"))-1)
>>
>>the file must be saved at least once before this formula will work.
>>
>>"Minitman" wrote:
>>
>>> Greetings,
>>>
>>> I have a lot of workbooks with a date as a name (eg. 2004-08.xls
>>> or
>>> 1999-03.xls). I would like to capture the date portion of this
>>> name
>>> with a formula in sheet 'Date' cell 'A4'.
>>>
>>> Anyone have any ideas?
>>>
>>> Your help is appreciated.
>>>
>>> TIA
>>>
>>> -Minitman
>>>
>
Hey Dave,
Thanks for the warning. It is appreciated.
Any chance you could help with the conversion problem. (eg. the
result of this formula looks like a date. But when put into a date
cell, the other cells that are linked to it are giving me the #VALUE!
error or "This is not a date!")
Any suggestions would be very helpful.
-Minitman
On Tue, 06 Dec 2005 15:10:59 -0600, Dave Peterson
<petersod@verizonXSPAM.net> wrote:
>Just a warning...
>
>Change each of the:
>CELL("filename")
>to
>CELL("filename",A1)
>
>Else you'll get the filename of the activeworkbook when excel calculated.
>
>Minitman wrote:
>>
>> Greetings,
>>
>> I have a lot of workbooks with a date as a name (eg. 2004-08.xls or
>> 1999-03.xls). I would like to capture the date portion of this name
>> with a formula in sheet 'Date' cell 'A4'.
>>
>> Anyone have any ideas?
>>
>> Your help is appreciated.
>>
>> TIA
>>
>> -Minitman
Hey Conan,
Thanks for the reply.
Good idea, but it also returns the #VALUE! error.
-Minitman
On Tue, 6 Dec 2005 14:46:42 -0700, "Conan Kelly" <CTBarbarin at msn
dot com> wrote:
>Mintman,
>
>Try to wrap the formula they sent you in the DATEVALUE() function:
>
>=DATEVALUE(MID(CELL("filename",A1),1+FIND("[",CELL("filename",A1)),FIND(".",CELL("filename",A1),FIND("[",CELL("filename",A1)))-FIND("[",CELL("filename",A1))-1))
>
>HTH,
>
>Conan
>
>
>
>
>"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
>news:ogvbp1dvrpegi2ev9qhjro27vpfha1hqvn@4ax.com...
>> Hey David and Sloth,
>>
>> Thanks guys. They both indeed return the text string for the file
>> name as you said. This maybe what I requested, it is,
>> unfortunately,
>> not seen as a date. Is there anyway to convert this string into a
>> fully functual date?
>>
>> TIA
>>
>> -Minitman
>>
>>
>>
>> On Tue, 6 Dec 2005 11:56:02 -0800, "Sloth"
>> <Sloth@discussions.microsoft.com> wrote:
>>
>>>=MID(CELL("filename"),1+FIND("[",CELL("filename")),FIND(".",CELL("filename"),FIND("[",CELL("filename")))-FIND("[",CELL("filename"))-1)
>>>
>>>the file must be saved at least once before this formula will work.
>>>
>>>"Minitman" wrote:
>>>
>>>> Greetings,
>>>>
>>>> I have a lot of workbooks with a date as a name (eg. 2004-08.xls
>>>> or
>>>> 1999-03.xls). I would like to capture the date portion of this
>>>> name
>>>> with a formula in sheet 'Date' cell 'A4'.
>>>>
>>>> Anyone have any ideas?
>>>>
>>>> Your help is appreciated.
>>>>
>>>> TIA
>>>>
>>>> -Minitman
>>>>
>>
>
Hey John,
That does the trick.
Thank you. My thanks to all of you.
-Minitman
On 6 Dec 2005 13:46:17 -0800, "John Michl" <waxwing1@gmail.com> wrote:
>I'd use this.
>
>=DATE(MID(CELL("filename"),FIND("-",CELL("filename"))-4,4),MID(CELL("filename"),FIND("-",CELL("filename"))+1,2),1)
>
>The final "1" in the formula indicates day one. You can format the
>result to show the date in any way desired.
>
>- John
>www.JohnMichl.com
How about:
=--(MID(CELL("filename",A1),1+FIND("[",CELL("filename",A1)),
FIND(".",CELL("filename",A1),FIND("[",CELL("filename",A1)))
-FIND("[",CELL("filename",A1))-1)&"-01")
(all one cell)
And format it as a date.
This converts 2004-12 to December 1, 2004.
Is that the date you wanted?
Minitman wrote:
>
> Hey Conan,
>
> Thanks for the reply.
>
> Good idea, but it also returns the #VALUE! error.
>
> -Minitman
>
> On Tue, 6 Dec 2005 14:46:42 -0700, "Conan Kelly" <CTBarbarin at msn
> dot com> wrote:
>
> >Mintman,
> >
> >Try to wrap the formula they sent you in the DATEVALUE() function:
> >
> >=DATEVALUE(MID(CELL("filename",A1),1+FIND("[",CELL("filename",A1)),FIND(".",CELL("filename",A1),FIND("[",CELL("filename",A1)))-FIND("[",CELL("filename",A1))-1))
> >
> >HTH,
> >
> >Conan
> >
> >
> >
> >
> >"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
> >news:ogvbp1dvrpegi2ev9qhjro27vpfha1hqvn@4ax.com...
> >> Hey David and Sloth,
> >>
> >> Thanks guys. They both indeed return the text string for the file
> >> name as you said. This maybe what I requested, it is,
> >> unfortunately,
> >> not seen as a date. Is there anyway to convert this string into a
> >> fully functual date?
> >>
> >> TIA
> >>
> >> -Minitman
> >>
> >>
> >>
> >> On Tue, 6 Dec 2005 11:56:02 -0800, "Sloth"
> >> <Sloth@discussions.microsoft.com> wrote:
> >>
> >>>=MID(CELL("filename"),1+FIND("[",CELL("filename")),FIND(".",CELL("filename"),FIND("[",CELL("filename")))-FIND("[",CELL("filename"))-1)
> >>>
> >>>the file must be saved at least once before this formula will work.
> >>>
> >>>"Minitman" wrote:
> >>>
> >>>> Greetings,
> >>>>
> >>>> I have a lot of workbooks with a date as a name (eg. 2004-08.xls
> >>>> or
> >>>> 1999-03.xls). I would like to capture the date portion of this
> >>>> name
> >>>> with a formula in sheet 'Date' cell 'A4'.
> >>>>
> >>>> Anyone have any ideas?
> >>>>
> >>>> Your help is appreciated.
> >>>>
> >>>> TIA
> >>>>
> >>>> -Minitman
> >>>>
> >>
> >
--
Dave Peterson
Hey Dave,
Thanks for the reply.
I could not get you code to work. But that's ok, John Michl's
solution solved the problem
-Minitman.
On Tue, 06 Dec 2005 17:04:41 -0600, Dave Peterson
<petersod@verizonXSPAM.net> wrote:
>How about:
>
>=--(MID(CELL("filename",A1),1+FIND("[",CELL("filename",A1)),
>FIND(".",CELL("filename",A1),FIND("[",CELL("filename",A1)))
> -FIND("[",CELL("filename",A1))-1)&"-01")
>
>(all one cell)
>
>And format it as a date.
>
>This converts 2004-12 to December 1, 2004.
>Is that the date you wanted?
>
>
>Minitman wrote:
>>
>> Hey Conan,
>>
>> Thanks for the reply.
>>
>> Good idea, but it also returns the #VALUE! error.
>>
>> -Minitman
>>
>> On Tue, 6 Dec 2005 14:46:42 -0700, "Conan Kelly" <CTBarbarin at msn
>> dot com> wrote:
>>
>> >Mintman,
>> >
>> >Try to wrap the formula they sent you in the DATEVALUE() function:
>> >
>> >=DATEVALUE(MID(CELL("filename",A1),1+FIND("[",CELL("filename",A1)),FIND(".",CELL("filename",A1),FIND("[",CELL("filename",A1)))-FIND("[",CELL("filename",A1))-1))
>> >
>> >HTH,
>> >
>> >Conan
>> >
>> >
>> >
>> >
>> >"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
>> >news:ogvbp1dvrpegi2ev9qhjro27vpfha1hqvn@4ax.com...
>> >> Hey David and Sloth,
>> >>
>> >> Thanks guys. They both indeed return the text string for the file
>> >> name as you said. This maybe what I requested, it is,
>> >> unfortunately,
>> >> not seen as a date. Is there anyway to convert this string into a
>> >> fully functual date?
>> >>
>> >> TIA
>> >>
>> >> -Minitman
>> >>
>> >>
>> >>
>> >> On Tue, 6 Dec 2005 11:56:02 -0800, "Sloth"
>> >> <Sloth@discussions.microsoft.com> wrote:
>> >>
>> >>>=MID(CELL("filename"),1+FIND("[",CELL("filename")),FIND(".",CELL("filename"),FIND("[",CELL("filename")))-FIND("[",CELL("filename"))-1)
>> >>>
>> >>>the file must be saved at least once before this formula will work.
>> >>>
>> >>>"Minitman" wrote:
>> >>>
>> >>>> Greetings,
>> >>>>
>> >>>> I have a lot of workbooks with a date as a name (eg. 2004-08.xls
>> >>>> or
>> >>>> 1999-03.xls). I would like to capture the date portion of this
>> >>>> name
>> >>>> with a formula in sheet 'Date' cell 'A4'.
>> >>>>
>> >>>> Anyone have any ideas?
>> >>>>
>> >>>> Your help is appreciated.
>> >>>>
>> >>>> TIA
>> >>>>
>> >>>> -Minitman
>> >>>>
>> >>
>> >
Sorry, but it did work ok for me.
Minitman wrote:
>
> Hey Dave,
>
> Thanks for the reply.
>
> I could not get you code to work. But that's ok, John Michl's
> solution solved the problem
>
> -Minitman.
>
> On Tue, 06 Dec 2005 17:04:41 -0600, Dave Peterson
> <petersod@verizonXSPAM.net> wrote:
>
> >How about:
> >
> >=--(MID(CELL("filename",A1),1+FIND("[",CELL("filename",A1)),
> >FIND(".",CELL("filename",A1),FIND("[",CELL("filename",A1)))
> > -FIND("[",CELL("filename",A1))-1)&"-01")
> >
> >(all one cell)
> >
> >And format it as a date.
> >
> >This converts 2004-12 to December 1, 2004.
> >Is that the date you wanted?
> >
> >
> >Minitman wrote:
> >>
> >> Hey Conan,
> >>
> >> Thanks for the reply.
> >>
> >> Good idea, but it also returns the #VALUE! error.
> >>
> >> -Minitman
> >>
> >> On Tue, 6 Dec 2005 14:46:42 -0700, "Conan Kelly" <CTBarbarin at msn
> >> dot com> wrote:
> >>
> >> >Mintman,
> >> >
> >> >Try to wrap the formula they sent you in the DATEVALUE() function:
> >> >
> >> >=DATEVALUE(MID(CELL("filename",A1),1+FIND("[",CELL("filename",A1)),FIND(".",CELL("filename",A1),FIND("[",CELL("filename",A1)))-FIND("[",CELL("filename",A1))-1))
> >> >
> >> >HTH,
> >> >
> >> >Conan
> >> >
> >> >
> >> >
> >> >
> >> >"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
> >> >news:ogvbp1dvrpegi2ev9qhjro27vpfha1hqvn@4ax.com...
> >> >> Hey David and Sloth,
> >> >>
> >> >> Thanks guys. They both indeed return the text string for the file
> >> >> name as you said. This maybe what I requested, it is,
> >> >> unfortunately,
> >> >> not seen as a date. Is there anyway to convert this string into a
> >> >> fully functual date?
> >> >>
> >> >> TIA
> >> >>
> >> >> -Minitman
> >> >>
> >> >>
> >> >>
> >> >> On Tue, 6 Dec 2005 11:56:02 -0800, "Sloth"
> >> >> <Sloth@discussions.microsoft.com> wrote:
> >> >>
> >> >>>=MID(CELL("filename"),1+FIND("[",CELL("filename")),FIND(".",CELL("filename"),FIND("[",CELL("filename")))-FIND("[",CELL("filename"))-1)
> >> >>>
> >> >>>the file must be saved at least once before this formula will work.
> >> >>>
> >> >>>"Minitman" wrote:
> >> >>>
> >> >>>> Greetings,
> >> >>>>
> >> >>>> I have a lot of workbooks with a date as a name (eg. 2004-08.xls
> >> >>>> or
> >> >>>> 1999-03.xls). I would like to capture the date portion of this
> >> >>>> name
> >> >>>> with a formula in sheet 'Date' cell 'A4'.
> >> >>>>
> >> >>>> Anyone have any ideas?
> >> >>>>
> >> >>>> Your help is appreciated.
> >> >>>>
> >> >>>> TIA
> >> >>>>
> >> >>>> -Minitman
> >> >>>>
> >> >>
> >> >
--
Dave Peterson
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks