Need formula to display values from past one year to current date

Swetha

I have a column with names Date Opened and Level. Need to check the value in Date opened is in between last year to current date. In the Required column, need data to be returned, if it falls between last year beginning of the month (where month needs to be same as current month) to Current System date.

Ex: If we are in FEB 2023 (no matter whatever the date in FEB 23), then value returned should check in-between FEB 1st 2022 to current month & date.

Kelly Moore

Hey @Swetha

Try this

=IF(ISDATE([Date Opened]@row), IF(AND([Date Opened]@row >= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1), [Date Opened]@row <= TODAY()), "NA", Level@row))

If "NA" isn't what you want returned when the date is between last year and today, change it to what you desire.

Will this work for you?

Kelly

Kelly Moore

Hey @Swetha

Try this

=IF(ISDATE([Date Opened]@row), IF(AND([Date Opened]@row >= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1), [Date Opened]@row <= TODAY()), "NA", Level@row))

If "NA" isn't what you want returned when the date is between last year and today, change it to what you desire.

Will this work for you?

Kelly

Swetha

@Kelly Moore : Hey it worked, I cant thank you enough!!!!

Swetha

@Kelly Moore : I need little tweak for the above formula; The value is returned for the date that is between last year and current year today. Is it possible to tweak the formula where value is returned for the date between last year (exact date for today's) to current date. I don't want it to consider from last year beginning date of current month.

Can you please look into this?

Swetha

@Kelly Moore : Can you please take a look?

Kelly Moore

Hey @Swetha

Try this

=IF(ISDATE([Date Opened]@row), IF(AND([Date Opened]@row >= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY([Date Opened]@row)), [Date Opened]@row <= TODAY()), "NA", Level@row))

Does this work for you?

Kelly