Directory Tool -Extract Date

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

Input

Accepted answers

HomesickSurfer

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

All comments

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703