Capture the Workbook Name

M

Minitman

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
 
D

David Billigmeier

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)
 
S

Sloth

=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.
 
D

Dave Peterson

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.
 
M

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



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

John Michl

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
 
C

Conan Kelly

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 said:
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



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

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
 
M

Minitman

Hey Conan,

Thanks for the reply.

Good idea, but it also returns the #VALUE! error.

-Minitman




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 said:
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



=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.

:

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

Dave Peterson

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?

Hey Conan,

Thanks for the reply.

Good idea, but it also returns the #VALUE! error.

-Minitman

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

=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.

:

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

Minitman

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.




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?

Hey Conan,

Thanks for the reply.

Good idea, but it also returns the #VALUE! error.

-Minitman

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




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"

=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.

:

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

Dave Peterson

Sorry, but it did work ok for me.
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.

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?

Hey Conan,

Thanks for the reply.

Good idea, but it also returns the #VALUE! error.

-Minitman

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




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"

=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.

:

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Link A Cell In O ne Workbook To A Formula In Another Workbook 0
display date of today in cell X when number >0 in cell Y 2
Reference a file by concatenating cell variables 13
Save As Name To Change Cell Value 0
Macro for summarizing multiple sheets 1
How to copy & paste a worksheet but change 1 bit of the formula? 1
Conditional Format Formula Help 2
Linking Daily Workbooks 1

Top