Filtering by moving date across multiple columns

[CaraM]

I have a feeling that this isn't complicated, but I'm just not thinking about it correctly. I have a sheet of my tasks, with lots of autofiltering going on via VBA that is working fine.

Now, I want to look at two different date columns (LastModified and StartDate) and filter my tasks where EITHER contains a recent (but not future) date. So:
Lastmodified > today()-1 OR (StartDate >=today()-1 AND StartDate <=today()+1)

I can't use AutoFilter for this, right, because it's an OR query against different fields?

I can't really use Advanced Filter, can I? Because the date is relative and not fixed. Plus, I really don't want to have to have a criteria range - it seems like I should be able to do this in code.

And -- is there some way to use PowerQuery through VBA to accomplish this?

Again - seems like this should be simple, but I cannot see what I'm missing.

I did TRY the Advanced Filter. I put LastModified and StartDate column headers in a new sheet, put this in my criteria range, and it looked weird at first but I believe the results were right. But that date has to move. Do I have to code a specific date entry into the criteria formulas? Is there a "cleaner" way?
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][TABLE="width: 164"]
<tbody>[TR]
[TD="class: xl66, width: 164"]LastModified[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 328"]
<tbody>[TR]
[TD="class: xl66, width: 328"]STARTDATE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]=">=2/19/2019"[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]=">=2/19/2019,<=2/20/2019"[/TD]
[/TR]
</tbody>[/TABLE]






Thanks

 

[sijpie]

Re: Questions about filtering by moving date across multiple columns

hi CaraM,

can you not use today() in your formula?
=">="&today() or
=">="&TEXT(TODAY(),"dd//mm/yy")

 

[CaraM]

Re: Questions about filtering by moving date across multiple columns

sijpie,

It sure seems like I should be able to. But I need >=yesterday in the LastModified criteria, and between yesterday and tomorrow in the StartDate criteria and, when I use today()-1 and today()+1 to try to get those date, I get no data returned, though I should.

In addition, it sure feels like mixing advanced filters and autofilters is messing with my code. I'm not able to determine when filters are on or off, since adding the Advanced Filter.

 

[sijpie]

Re: Questions about filtering by moving date across multiple columns

yesterday = today()-1

so the formula would become:
=">="&TEXT(TODAY()-1,"DD/MM/YY")

 

[CaraM]

Re: Questions about filtering by moving date across multiple columns

Thank you so much!