Extracting Date from Filename

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

Workflow

Date Time

Accepted answers

Rana_Kareem

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

All comments

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.

Extracting Date from FileName.PNG Regex config.PNG DateTime parse config.PNG Select config1.PNG Extraction Output.PNG

Extracting Date from FileName.yxmd

Rana_Kareem

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703