Directory Tool -Extract Date

[suby]

Hi All,

I'm using a Directory tool to bring 2 files by using the  FileName Output column from the Directory tool I want to extract the date and put the date in new column and this I what I have in my formula tool

Formula

FileName (Output From Directory tool)

Right([Filename],10)

Formula

Date (New Column)
DateTimeFormat([FileName],'%d/%m/%Y')

I get NULL values Any thoughts around this

File Names looks as below

xxxxxxxx_xxx_xxxxx_2020-08-31.xlsx
xxxxxxxx_xxx_xxxxx_2020-09-20.xlsx

[T_Willins]

Hi @suby,

Based on your formulas your Right([Filename],10) is going to return "08-31.xlsx".  Try using Left(Right([Filename],15),10)  This should give you "2020-08-31" that you can apply the second formula to.  

[suby]

Hi,

Thanks for your reply and still brings up Null Values.

[HomesickSurfer]

Hi @suby 

Try:  DateTimeFormat(ToDate(Right(FileGetFileName([FileName]), 10)),'%d/%m/%Y')

Hope this works for you...

[suby]

Hello,

Great this works Thank you so Much.

Could you please explain me how the syntax works.

[HomesickSurfer · Accepted solution]

Hi @suby 

I'm pleased.  Please like and accept as an acceptable solution.  Thx 🙂

DateTimeFormat(ToDate(Right(FileGetFileName([FileName]), 10)),'%d/%m/%Y')

FileGetFileName([FileName]) removes the file extension and '.'

(Right(FileGetFileName([FileName]), 10)) captures the last 10 characters

ToDate(Right(FileGetFileName([FileName]), 10)) converts to date format

DateTimeFormat(ToDate(Right(FileGetFileName([FileName]), 10)),'%d/%m/%Y') formats the date as specified dd/mm/yyyy

[suby]

Thank you so Much for the explanantion.