Need formula to display values from past one year to current date
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.
Best Answer
-
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
Answers
-
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 it worked, I cant thank you enough!!!!
-
@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?
-
@Kelly Moore : Can you please take a look?
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!