ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Capture the Workbook Name (https://www.excelbanter.com/excel-worksheet-functions/59068-capture-workbook-name.html)

Minitman
Capture the Workbook Name
 
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

David Billigmeier
Capture the Workbook Name
 
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

Sloth
Capture the Workbook Name
 
=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
Capture the Workbook Name
 
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

Minitman
Capture the Workbook Name
 
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"
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

John Michl
Capture the Workbook Name
 
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

Conan Kelly
Capture the Workbook Name
 
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" wrote in message
...
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"
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

Minitman
Capture the Workbook Name
 
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
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

Minitman
Capture the Workbook Name
 
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" wrote in message
.. .
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"
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

Minitman
Capture the Workbook Name
 
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" 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

Dave Peterson
Capture the Workbook Name
 
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" wrote in message
.. .
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"
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

Minitman
Capture the Workbook Name
 
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
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" wrote in message
.. .
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"
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
Capture the Workbook Name
 
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
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" wrote in message
.. .
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"
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


All times are GMT +1. The time now is 03:04 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com