Dates range in formula function

Question

Hi Team

I have a data and want to filter the data date range in between  (Yesterday, Today and tomorrow) permanently like today is 14th Jan 2023. so automation must filter the dates like these (yesterday 13th Jan , Today 14th Jan and tomorrow 15th Jan 2024). so as per the date it should filter like this.

kindly help me with the formula tools and method to adjust in my data set. let me know if you need more clarification about this.

thanks

Vipul

Hammad_Rashid · Answer

Assuming your date column is named "date_column" and the date format is in a standard format like 'YYYY-MM-DD', you can create a new column to identify whether each date falls into the specified range.

Here is an example formula:

if(date_column == dateadd(now(),-1,"day") || date_column == now() || date_column == dateadd(now(),1,"day"), "In Range", "Outside Range")

This formula uses the dateadd function to calculate yesterday and tomorrow based on the current date (now() function). The if statement checks if the date in the "date_column" is equal to yesterday, today, or tomorrow. If it matches, the new column will have the value "In Range"; otherwise, it will be "Outside Range".

Make sure to adjust the column names and date formats based on your actual dataset. You can then use this new column to filter your data further.

AkimasaKajitani · Answer

Hi @vipulsharma ,

If you want to get today's date, you can use DateTimeNow() expression. And also, to get the tomorrow and yesterday, you can use DateTimeAdd expression. And then, when you want to compare the date unit, you need to ToDate expression.

I think that you can set the expression below on Advanced filter of the Filter tool.

[Column1]>=ToDate(DateTimeAdd(DateTimeTrim(DateTimeNow(),"Day"),-1,"Day"))
AND 
[Column1]<=ToDate(DateTimeAdd(DateTimeTrim(DateTimeNow(),"Day"),1,"Day"))

If you have any questions, please let me know.