Extracting Date from Filename

[KamenRider]

Guys,

I have this formula "DateTimeParse(REGEX_Replace([FileName], '[^\d{8}]', ''),'%Y%m%d')" but it is not working in this filename >>>>  LLB.Pest.Daily_08.10.23_FINAL.xlsx. It gaves me "Null". Can someone please help me how to extract the date with same date format? Please do also explain the formula you will be providing so for me to understand it.

Thanks,

Kamen

[Prometheus]

I like to use www.regex101.com to test. Check it out. It helps me a lot.

I started out the workflow with the RegEx tool configured to Parse out a single field with the default name of RegExOut1. I used the expression .*\_(\d{2}\.\d{2}\.\d{2}).* because your filename has two digits for year, two digits for month, and two digits for day. The only thing that gets parsed is what's inside the parentheses. After that, I dropped in a DateTime parse tool to turn the date format of mm.dd.yy into an actual date. I finished with a Select tool to manually change the data type from DateTime, which is the default out of the DateTime parse tool, to Date.

[Rana_Kareem · Accepted solution]

Hi @KamenRider ..

Just modify the format In DateTimeParse Function, so it matches the incoming date format from the File Name.

After extracting the Date from the File Name with (REGEX_Replace) Function it will be something like this (081023)

So, the correct format for this:  %m%d%y

Therefore, you have to modify the formula to be:

DateTimeParse(REGEX_Replace([FileName], '[^\d{8}]', ''),'%m%d%y')

Hope that's clear..

You can find more about DateTime Functions Here

[KamenRider]

Thank you for your expertise. I will try to learn this one as this is always my challenges.

Kamen