get Date from file name

Seamus2626
Seamus2626 used Ask the Experts™
on
Hi,

I have a file name thats in this format

"DIV rec ddmmyyyy.xls"

I want a piece of formula to retrieve the date from this, so i can return ddmmyyyy

Thanks
Seamus
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Seamus, try this - =mid("filename",9,8)

Author

Commented:
Thing is runrigger, i wont be able to put the "filename" in as the report will be automated, so its going to have to read the file name and then cut it

Thanks
Seamus
Most Valuable Expert 2011
Awarded 2010
Commented:
try

=RIGHT(LEFT(MID(CELL("filename"),FIND("[",CELL("filename"))+1,99),FIND(".xls",MID(CELL("filename"),FIND("[",CELL("filename"))+1,99))-1),8)

This will take the current file name.

cheers, teylyn
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Top Expert 2008

Commented:
On the off chance you are working in VBA, use this code to convert the file name in string "X":

   MyDate = DateSerial(Mid(X, 13, 4), Mid(X, 11, 2), Mid(X, 9, 2))

Kevin
Most Valuable Expert 2011
Awarded 2010

Commented:
The formula I posted above will extract the last 8 characters from any file name, just before the .xls or .xlsx extension, regardless of how long the file name is.
Seamus, teylyn's solution is the way to go

The formula - CELL("Filename") gives you the full "Path\Filename.xls\Sheet"

Teylyn's formula, strips out the fielname element and then strips out the date element.

Author

Commented:
Hi kevin, i can use VBA, what do you mean convert the filename in String "X"?

How i would i place this in the editor, could i use

 .Range("U1").Formula = " MyDate = DateSerial(Mid(X, 13, 4), Mid(X, 11, 2), Mid(X, 9, 2))"

Thanks
Seamus
very clever Teylyn ;-)
Seamus, no need for VBA with Teylyn's formula!
Top Expert 2008

Commented:
Seamus2626,

My code is not a formula that is compatible with an Excel formula. If you want to add a formula to the worksheet you can use VBA BUT you need to use a real cell formula which mine is not.

That said, what is the source of the file name? Is it a text string in a cell? Or do you want the date from the workbook's file name?

Kevin

Author

Commented:
I can use Teylyn's solution, i was hoping that using the vba method may return the string in date format, im ageing some breaks using that date, so at the moment i cant compare the returned date with another date as the returned date is formatted General i believe, if the VBA method doesnt return in a date format, i can create a workaround

Kevin, yeah, looking to return the filenames date ideally in date format.

So todays file is Div rec 29042011.xls

I would like returned (ideally in date format) 29042011

Thanks
Seamus
Top Expert 2008
Commented:
If you want to use VBA then you can use this line to put the file's date in the cell:

   .Range("U1").Value = DateSerial(Mid(ThisWorkbook.Name, 13, 4), Mid(ThisWorkbook.Name, 11, 2), Mid(ThisWorkbook.Name, 9, 2))

Kevin
Not points for me - all to teylyn;

=DATEVALUE(RIGHT(LEFT(MID(CELL("filename"),FIND("[",CELL("filename"))+1,99),FIND(".xls",MID(CELL("filename"),FIND("[",CELL("filename"))+1,99))-1),8))

Author

Commented:
Thanks everyone, thank you too Runrigger, i went with the VBA as it does the formatting.

Thanks guys,
Seamus

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial