How to filter data based on current date

Question

I have an Excel file with 3 tabs. The first tab is data filling in from a Microsoft Form, this data includes a column with a date field called "Date of Event".  The second tab is called Future Events and the third tab is called Past Events.  I copied the table from the Forms tab and pasted special using a link to the Future and Past Events tab. That all worked great. Now I need the rows on the Future Events tab to only display if their Date of Event is greater than or equal to the current date.  I need the rows on the Past Events tab to display only if they are less than the current date.  I have attempted to use the Advanced formatting, but it will only allow you to select an actual date, the Today() formula does not appear to be working. I attempted to use Data Validation and while you can add a validation for Date is greater than or equal to Today(), it does not see my column as a Date field and so is doing this validation on all fields in the table. Any other ideas?

Answer 1

Thanks all, I found a way to do this through using the Power Query editor and adding a custom column with the formula: DateTime.LocalNow() to get today's date for comparison purposes.

Answer 2

Hi,

In cell A2 of the Future Events worksheet, enter this formula

=filter('sheet1'A2:E6,'sheet1'D2:D6>today())

Hope this helps.