Need formula to return a value for a year less than current year and less than current month
If the year is less than current year and month is lesser than current month, value return should be "N/A" along with the below mentioned combinations.
However, i have formula for below mentioned combination; i just need to add the above scenario for the formula.
=IFERROR(IF(VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 3, 2)) > VALUE(RIGHT(YEAR(TODAY()), 2)), "NA", IF(AND(VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 3, 2)) = VALUE(RIGHT(YEAR(TODAY()), 2)), VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 5, 2)) > VALUE(MONTH(TODAY()))), "NA", RI@row)), RI@row)
If year is greater than the current year and equal to current year but month is greater then current month value return is "N/A".
If the year is equal to current year but less than or equal to current month, exact value from RI column should be returned.
If the year is less than current year and month is equal to or greater than current month, exact value from RI column should be returned.
Best Answer
-
=IFERROR(IF(OR(VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 3, 2)) > VALUE(RIGHT(YEAR(TODAY()), 2)), AND(VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 5, 2)) < MONTH(TODAY()), VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 3, 2)) < VALUE(RIGHT(YEAR(TODAY()), 2)))), "NA", IF(OR(AND(VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 3, 2)) = VALUE(RIGHT(YEAR(TODAY()), 2)), VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 5, 2)) > VALUE(MONTH(TODAY()))), VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 3, 2)) < VALUE(RIGHT(YEAR(TODAY()), 2)) - 1), "NA", RI@row)), RI@row)
Answers
-
@Kelly Moore : Could you please look into this
-
@Paul Newcome : Can you please look into this formula too. i need to add one more scenario for existing formula.
If the year is less than current year and month is lesser than current month, value return should be "N/A" along with the below mentioned combinations.
However, i have formula for below mentioned combination; i just need to add the above scenario for the formula.
=IFERROR(IF(VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 3, 2)) > VALUE(RIGHT(YEAR(TODAY()), 2)), "NA", IF(AND(VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 3, 2)) = VALUE(RIGHT(YEAR(TODAY()), 2)), VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 5, 2)) > VALUE(MONTH(TODAY()))), "NA", RI@row)), RI@row)
If year is greater than the current year and equal to current year but month is greater then current month value return is "N/A".
If the year is equal to current year but less than or equal to current month, exact value from RI column should be returned.
If the year is less than current year and month is equal to or greater than current month, exact value from RI column should be returned.
In the below example for the row highlighted in yellow; NA needs to be displayed.
-
Hey @Swetha
Try this
=IFERROR(IF(OR(VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 3, 2)) > VALUE(RIGHT(YEAR(TODAY()), 2)), AND(VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 5, 2)) < MONTH(TODAY()), VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 3, 2)) < VALUE(RIGHT(YEAR(TODAY()), 2)))), "NA", IF(AND(VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 3, 2)) = VALUE(RIGHT(YEAR(TODAY()), 2)), VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 5, 2)) > VALUE(MONTH(TODAY()))), "NA", RI@row)), RI@row)
Kelly
-
@Kelly Moore : Thanks for the reply. I used the formula. If the year is less than current year and month is lesser than current month, then "NA" value is returned for months with 01 & 02. However, if If the year is less than current year and month is lesser than current month with values 03 to 12 then value is returned. I need "NA" to be returned for this scenario too. Remaining formula works fine
Can you please take a look? The row highlighted in blue needs "NA" to be returned.
-
@Kelly Moore : I'm not sure, whether i have asked the formula request correctly. I'm looking for a formula, if If the year is less than current year and month is lesser than current month, value return should be "N/A" a along with other criteria's.
will try to explain with example. If year is less than current year i.e if it falls with in last one year with exact current month then value should be returned. i.e. CL2203--> CL2203. CL2206--->CL2206. However, if it is before one year then "NA" should be returned. i.e. OL2202---> NA, CL2105--->NA, DL2102--> DL2102, OL1906--> NA.
With this formula, OL1906 returns OL1906, CL2105 returns CL2105. i need NA for this condition.
Remaining part of formula works fine.
If i need to simplify, if year is with in last 365 days till date(current month) then value needs to be returned otherwise it should be NA.
Could you please take a look and help me on this.
-
Hey @Swetha
Try this
=IFERROR(IF(OR(VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 3, 2)) > VALUE(RIGHT(YEAR(TODAY()), 2)), AND(VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 5, 2)) <= MONTH(TODAY()), VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 3, 2)) < VALUE(RIGHT(YEAR(TODAY()), 2)))), "NA", IF(AND(VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 3, 2)) = VALUE(RIGHT(YEAR(TODAY()), 2)), VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 5, 2)) > VALUE(MONTH(TODAY()))), "NA", RI@row)), RI@row)
What value do you get now?
Kelly
-
@Kelly Moore : If it less than or equal to last year and month is 01,02& 03 NA is displayed. However, If it is last year i.e. 22 & Current month i.e. 03 "NA" should not be displayed, need to return value.
If it is less than last year (i.e.22) and month is 04,05,06,07,08,09,10,11&12 same value is returned. However, NA needs to be displayed.
-
@Kelly Moore : Is it possible to look at this formula?
-
@Kelly Moore : Is there any way you can look at this, i really need this to be fixed. Could you please let me know
-
=IFERROR(IF(OR(VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 3, 2)) > VALUE(RIGHT(YEAR(TODAY()), 2)), AND(VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 5, 2)) < MONTH(TODAY()), VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 3, 2)) < VALUE(RIGHT(YEAR(TODAY()), 2)))), "NA", IF(OR(AND(VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 3, 2)) = VALUE(RIGHT(YEAR(TODAY()), 2)), VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 5, 2)) > VALUE(MONTH(TODAY()))), VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 3, 2)) < VALUE(RIGHT(YEAR(TODAY()), 2)) - 1), "NA", RI@row)), RI@row)
-
@Kelly Moore : Thank you Very much!! It worked!!!
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!