Output data for Two months

[suby]

Hi All,

I have  daily .xlsx files which are stored in shared in out network shared drive that directory will be the main source of data.

All daily files have multiple tabs and I'm interested in picking up at least say 4 to 5 Tabs for my analysis and the column structure are different.

Sample Directory Structure

xxxxxx_xxxx_xxxx_xx_xx-xx-xx_2021-02-28xxxxxxxx.xlsx   ( 28th of Feb file)

xxxxxx_xxxx_xxxx_xx_xx-xx-xx_2021-03-01xxxxxxxx.xlsx   (1st of March file)

xxxxxx_xxxx_xxxx_xx_xx-xx-xx_2021-03-02xxxxxxxx.xlsx   (2nd of March file)

xxxxxx_xxxx_xxxx_xx_xx-xx-xx_2021-04-01xxxxxxxx.xlsx   ( 1st of April file)

xxxxxx_xxxx_xxxx_xx_xx-xx-xx_2021-04-02xxxxxxxx.xlsx   ( 2nd of April file)

xxxxxx_xxxx_xxxx_xx_xx-xx-xx_2021-04-30xxxxxxxx.xlsx   ( 30th of April file)

xxxxxx_xxxx_xxxx_xx_xx-xx-xx_2021-05-01xxxxxxxx.xlsx   (1st of May  file)

Ultimate goal is to compare two months of data set at any given point in time also when I produce the output file in my workflow I just want to have only two months of data set based on the date say 

since we are in April 27th  -  I want to compare   (1st of March file)  with   ( 1st of April file)

When we are in May 27th -  I want to compare   ( 1st of April file)    with   (1st of May  file)  - the output should contain only 1stof April and 1st of May

when we are in June 27th -   I want to compare   (1st of May  file)   with (1st of June file)  - the output should contain only 1st of May and 1st of June

Ultimate the out put file should hold the data only for those two months and the reason for that due to record volume we want to keep only two months of data.

is this possible to achieve i mean say for example when we are in 27th of June we just need to keep the output should contain only 1st of May and 1st of June which means the historic month data for the 1st of April should be removed from the output file

is this possible to achieve

Many thanks

[apathetichell]

Here is what I posted on the thread you started yesterday.

Here's the datetime code to take today's date and get the first of last month, and the first of the previous month:

datetimeadd(datetimetoday(),-tonumber(datetimeformat(datetimetoday(),"%e"))+1,"days")

and:

datetimeadd(datetimeadd(datetimetoday(),-tonumber(datetimeformat(datetimetoday(),"%e"))+1,"days"),-1,"months")

You add these fields in a formula tool (with tostring() to convert them into strings) into placeholders on your filenames in a formula tools to dynamically generate the file names.

When placed in formula these formulas will generate the dates which you are looking for - ie the first of this month and the previous month. Then you can generate the filenames to match your syntax.

[apathetichell]

Here's the datetime code to take today's date and get the first of last month, and the first of the previous month:

datetimeadd(datetimetoday(),-tonumber(datetimeformat(datetimetoday(),"%e"))+1,"days")

and:

datetimeadd(datetimeadd(datetimetoday(),-tonumber(datetimeformat(datetimetoday(),"%e"))+1,"days"),-1,"months")

You add these fields in a formula tool (with tostring() to convert them into strings) into placeholders on your filenames in a formula tools to dynamically generate the file names.

When placed in formula these formulas will generate the dates which you are looking for - ie the first of this month and the previous month. Then you can generate the filenames to match your syntax.

[suby]

Hello,

Thanks for your input but i'm struggling to start can it be possible to have a small mockup please.

Thanks

[apathetichell]

filename divided into two parts. Dates formula. add.