| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| HOW DO I SUM TWO CELLS FROM ONE WORKBOOK TO ANOTHER WORKBOOK? | Excel Worksheet Functions | |||
| Multiple Workbook Data Capture Summary Sheet | Excel Discussion (Misc queries) | |||
| Linking a cell to another workbook cell based on a variable name | Excel Discussion (Misc queries) | |||
| Unprotect Workbook | Excel Discussion (Misc queries) | |||
| Stubborn toolbars in Excel | Excel Discussion (Misc queries) | |||
Linear Mode
