Need formula to return a value for a year less than current year and less than current month

Options
Swetha
Swetha ✭
in Formulas and Functions

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.

image.png


· Share on FacebookShare on Twitter

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    =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)

    · Share on FacebookShare on Twitter

Answers

  • Swetha
    Swetha ✭
    Options

    @Kelly Moore : Could you please look into this

    · Share on FacebookShare on Twitter
  • Swetha
    Swetha ✭
    edited 03/24/23
    Options

    @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.

    image.png


    · Share on FacebookShare on Twitter
  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    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

    · Share on FacebookShare on Twitter
  • Swetha
    Swetha ✭
    Options

    @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.

    image.png


    · Share on FacebookShare on Twitter
  • Swetha
    Swetha ✭
    Options

    @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.

    image.png


    · Share on FacebookShare on Twitter
  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 03/28/23
    Options

    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

    · Share on FacebookShare on Twitter
  • Swetha
    Swetha ✭
    edited 03/29/23
    Options

    @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.

    image.png


    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.

    image.png


    · Share on FacebookShare on Twitter
  • Swetha
    Swetha ✭
    Options

    @Kelly Moore : Is it possible to look at this formula?

    · Share on FacebookShare on Twitter
  • Swetha
    Swetha ✭
    Options

    @Kelly Moore : Is there any way you can look at this, i really need this to be fixed. Could you please let me know

    · Share on FacebookShare on Twitter
  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    =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)

    · Share on FacebookShare on Twitter
  • Swetha
    Swetha ✭
    Options

    @Kelly Moore : Thank you Very much!! It worked!!!

    image.png


    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!