Date filter question

[barne_as]

I have an excel report which shows data for the last week. The report is from a fixed source which I cannot change. Sometimes the report has entries for activity outside that week, so for example, I am interested in data from only Monday to Sunday but sometimes some events come through from the previous Sunday or the next Monday. I need help with the formula for a dynamic filter to parse out the dates not required. 

I already have columns for the day name and date but am struggling to write a dynamic formula to change each week. For context the source file name also contains the dates I need to filter on (ie, Weekly Report 04-07 to 04-13)

[DavidSkaife]

Hi @barne_as 

Could you share what you have built already?

[OTrieger]

@barne_as 

You need to set a point in time that you can use to evaluate if you have the correct week.

Let' say that every week the automation will run on Tuesday and the prior week will be the one that is needed.

You can use the Formula tool to get today's date, Today(). As you know that the automation will run on Tuesday you can filter out all the dates that are bigger than Today - 2 days

[Praveenmitta]

Date filter is very useful tool for every topic. it is easy to learn and use alsoo. please use it. 

[barne_as]

So that would mean I would need to have the workflow run at the same time each week. However the report is received at different times (its manually run by another department) and therefore the runtime of the workflow is variable. 

I can introduce a date time now input tool but can I create a dynamic formula to filter on the last week no matter what day the workflow is run?

[barne_as · Accepted solution]

I think I've solved it;

Filter for MON, then summarize with the Min date value of MON, filter for SUN then filter for max date of SUN then two appends to add columns ReportStart and ReportEnd back to the main data, then 2 x filter for Date >= ReportStart and Date <= ReportEnd. 

Maybe not the most elegant but it works. Any suggestions to optimize appreciated!

[tkemp]

Since run time is variable and assuming you want it to automatically reference a "start of week" date (e.g. not inputting the reference date via a Text Input tool), you could dynamically find a start of week on the same day by referencing the following formulas: 

[TestDate]: DateTimeToday() -- this is 4/17/2025 today

[Ref Weekday]: ToNumber(DateTimeFormat(DateTimeToday(), %u))  -- this returns the weekday as a number. It is 4 today which means it is Thursday. 

[Offset Date]: DateTimeAdd( [TestDate], -[Ref Weekday], 'days') -- Don't forget the negative sign in front of [Ref Weekday] or it will add days. 

This effectively subtracts the Weekday from Today's date to find a consistent "start of this week date" (4 days earlier than today would be 4/13). If the report goes farther back, you could subtract some multiple of 7 (e.g. another week) from [Ref Weekday] in the DateTimeAdd formula. If you want it to start on Monday (4/14) instead of Sunday (4/13), you could add 1 (or another value to get other days) to [Ref Weekday] in the DateTimeAdd formula.