Date Time difference returns incorrect result

[TFish]

Hi everyone, 

I am building a workflow, which will pull file names from a directory. My goal is to extract the date from file names and then make some comparisons or filtering, to make sure the only file I get is the one from yesterday (or can be the day before yesterday). I had 3 attempts, but none of them gave me any results.

The file name will be something like this: Extract 2024-03-22.xlsx. I got the date and also changed the field of DateTime_Out to Date. However, when I make the DateTimeDiff in formula between DateTime-Out and DateTimeToday, it doesn't give me correct result.

I have 2 questions:

1. Why doesn't the DateTimeDiff work?

2. Why don't my formulas in the Filter tool and Formula tool work?

3. What would be the best practice for my use case? I prefer writing a Regex for the filter tool to make it simple instead of having the Reg Ex tool and then having to do all the conversions.

[alexnajm]

1. Because the way you calculated it would result in -6 days, not six days. I would flip your columns around! DateTimeDiff(DateTimeToday(),[DateTime_Out],'days')

2. Not sure what your formula is in the filter tool - can you clarify?

3. Not sure what tool configuration this is referring to - can you clarify?

A workflow would be the most useful way for us to help out further! You can replace the Directory tool with a Text Input tool of a sample of your files

[usmanbashir]

@TFish 

1) Your DateTimeDiff isn't working because the difference is outputting a negative number. Flip to DateTimeDiff(DateTimeToday(),[DateTime_Out],'days')

2) Your filter isn't because your first filter RegexMatch is trying to match on exactly a date '####-##-##', however your filename will have an extension (in this case, .xlsx). Try using REGEX_Match([FileName], ".*\d{4}-\d{2}-\d{2}.*"). That takes any character before and after the date in the filename. This is same for your 'blablah' formula. It's trying to match on exactly 4 digits only. 

3) Best practice in this scenario just depends on nuance of files. If for example you have 10 files in a directory, you want to extract out the dates from the file names then filter out any other files that do not have a date in the name nor incorrect file extension. Next sort the dates descending and/or filter to the exact date you're looking for using your regex. Depending on the type of files within the directory, you might want to more validation to ensure you have the correct file. If the files are pretty standardized, Regex in filter could be all you need.

Hope this helps!