Filter using =Today()

Ricardo Siu

I would like to create a filter that shows tasks scheduled for today. Please see attached screenshot.

Carson Penticuff

I do not believe you can setup the filter the way you want it directly. The best you will be able to do, I believe, is the following:

That will get you close, but it will not include tasks that Start or Finish TODAY.

To get around this, you can add a helper column and use a formula to determine whether TODAY falls within the task timeline.

This is the formula that would go in the helper column.

=IF(AND([Start]@row <= TODAY(), [Finish]@row >= TODAY()), 1, 0)

Then, you just need to setup your filter.

You can also hide the helper column once you have it working.

Carson Penticuff

From my own testing, TODAY() appears to calculate based on the timezone of the person viewing the sheet. Have you verified your timezone is set correctly in your personal preferences? I'm not sure what will happen if multiple users with different timezone settings have the sheet open at the same time.

Carson Penticuff

I do not believe you can setup the filter the way you want it directly. The best you will be able to do, I believe, is the following:

That will get you close, but it will not include tasks that Start or Finish TODAY.

To get around this, you can add a helper column and use a formula to determine whether TODAY falls within the task timeline.

This is the formula that would go in the helper column.

=IF(AND([Start]@row <= TODAY(), [Finish]@row >= TODAY()), 1, 0)

Then, you just need to setup your filter.

You can also hide the helper column once you have it working.

Ricardo Siu

Hi Carson,

Thank you for your prompt reply. The suggestion kind of work, but I noticed that the =Today() formula uses a date that is not my current timezone. I suspect it is GMT-8 (EST). I am in GMT+8, Is there away to workaround this? Or better yet, setup my sheet to use my current timezone?

BR,

Ricardo

Carson Penticuff

From my own testing, TODAY() appears to calculate based on the timezone of the person viewing the sheet. Have you verified your timezone is set correctly in your personal preferences? I'm not sure what will happen if multiple users with different timezone settings have the sheet open at the same time.