Making filter or dynamic

Hi all,

I currently have a manual filter like so that I have to keep changing every week:

[StartDate] >= "2023-01-01" AND
[StartDate] <= "2023-11-13"

What I want to id make this filter a bit more dynamic meaning instead, the second condition should always be the Monday of the current week so instead of me having to manually change it i was hoping to put some logic behind it so that it can do dynamically itself. The first part will never change but the second condition will go to the current week i.e for next week manually it would be:

[StartDate] >= "2023-01-01" AND
[StartDate] <= "2023-11-20"

I've tried various approaches with the DateTime function but no dice yet any help would be greatly appreciated

Workflow

Date Time

Help

Accepted answers

DataNath

@Deano478 definitely feels like there's a more elegant expression here but this will dynamically give you the Monday of the current week so you can just replace your "2023-11-13"/"2023-11-20" value with this:

ToDate(
DateTimeAdd(DateTimeToday(),
-(ToNumber(
DateTimeFormat(
DateTimeToday(),'%u'))-1),
'day'))

All comments

DataNath

ToDate(
DateTimeAdd(DateTimeToday(),
-(ToNumber(
DateTimeFormat(
DateTimeToday(),'%u'))-1),
'day'))

Deano478

@DataNath Cheers for taking the time to have a look at this for me just so i can be clear in my own mind what odes this expression do?😀

DataNath

No problem @Deano478 - I can appreciate it looks a bit of a mess!

DateTimeFormat() with the %u argument returns the date's day of the week (1-7 with 1 being Monday)
The above results in a string i.e. today would be '2', hence we wrap this in ToNumber()
We need this as a number as it's going to be used in the DateTimeAdd() calc
To get back to Monday we'll always want to take the numeric day of the week -1, hence the -1 after the ToNumber()
The - before wrapped parentheses is because in the DateTimeAdd() calc, we want to go backwards rather than forwards
DateTimeAdd() just takes away N days where N is what we've calc'd above
ToDate() is used to force the result into a date as the outcome of DateTime... functions will always give a DateTime data type which leads to issues with comparisons against pure dates

Hope this helps!

Deano478

@DataNath seeing it like that makes a lot of sense many thanks for taking the time again to explain the expression it makes a lot more sense to me now.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703