<div>
Seamus, try this - =mid(&quot;filename&quot;,9,8)
</div>


<div>
Thing is runrigger, i wont be able to put the &quot;filename&quot; in as the report will be automated, so its going to have to read the file name and then cut it<br />
<br />
Thanks<br />
Seamus
</div>


<div>
On the off chance you are working in VBA, use this code to convert the file name in string &quot;X&quot;:<br />
<br />
&nbsp; &nbsp;MyDate = DateSerial(Mid(X, 13, 4), Mid(X, 11, 2), Mid(X, 9, 2))<br />
<br />
Kevin
</div>


<div>
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.
</div>


<div>
Seamus, teylyn's solution is the way to go<br />
<br />
The formula - CELL(&quot;Filename&quot;) gives you the full &quot;Path\Filename.xls\Sheet&quot;<br />
<br />
Teylyn's formula, strips out the fielname element and then strips out the date element.
</div>


<div>
Hi kevin, i can use VBA, what do you mean convert the filename in String &quot;X&quot;?<br />
<br />
How i would i place this in the editor, could i use<br />
<br />
&nbsp;.Range(&quot;U1&quot;).Formula = &quot;&nbsp;MyDate = DateSerial(Mid(X, 13, 4), Mid(X, 11, 2), Mid(X, 9, 2))&quot;<br />
<br />
Thanks<br />
Seamus
</div>


<div>
Seamus, no need for VBA with Teylyn's formula!
</div>


<div>
Seamus2626,<br />
<br />
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.<br />
<br />
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?<br />
<br />
Kevin
</div>


<div>
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<br />
<br />
Kevin, yeah, looking to return the filenames date ideally in date format.<br />
<br />
So todays file is Div rec 29042011.xls<br />
<br />
I would like returned (ideally in date format) 29042011<br />
<br />
Thanks<br />
Seamus
</div>


<div>
Not points for me - all to teylyn;<br />
<br />
=DATEVALUE(RIGHT(LEFT(MID(<wbr />CELL(&quot;file<wbr />name&quot;),FIN<wbr />D(&quot;[&quot;,CELL<wbr />(&quot;filename<wbr />&quot;))+1,99),<wbr />FIND(&quot;.xls<wbr />&quot;,MID(CELL<wbr />(&quot;filename<wbr />&quot;),FIND(&quot;[<wbr />&quot;,CELL(&quot;fi<wbr />lename&quot;))+<wbr />1,99))-1),<wbr />8))<br />
<br />

</div>


<div>
Thanks everyone, thank you too Runrigger, i went with the VBA as it does the formatting.<br />
<br />
Thanks guys,<br />
Seamus
</div>


<div>
<div class="content wysiwyg-content">
Hi,<br />
<br />
I have a file name thats in this format<br />
<br />
&quot;DIV rec ddmmyyyy.xls&quot; <br />
<br />
I want a piece of formula to retrieve the date from this, so i can return ddmmyyyy<br />
<br />
Thanks<br />
Seamus
</div>
</div>


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

get Date from file name

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.