Need a formula to return a value by appending month & year in number

I have column "Year" with text values(some text with -) and year + month values.. I need formula where value to be return in "Target" column by substituting hyphen(-) with year & month value.

For ex:


Could someone help me in retrieve the value in appending year & month by removing hyphen and month name at last.

Best Answer

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

    Hey @Swetha

    See if this satisfies your conditions

    =IF(OR(LEN(Year@row) < 6, AND(IFERROR(ISNUMBER(VALUE(RIGHT(Year@row, 1))), false), LEFT(Year@row, 4) = "TEST")), Year@row, IF(FIND("-", Year@row) > 0, LEFT(Year@row, FIND("-", Year@row) - 1) + (MID(Year@row, FIND("-", Year@row) + 1, 2)) + (IF(RIGHT(Year@row, 3) = "JAN", "01 Jan", IF(RIGHT(Year@row, 3) = "FEB", "02 Feb", IF(RIGHT(Year@row, 3) = "MAR", "03 Mar", IF(RIGHT(Year@row, 3) = "APR", "04 Apr", IF(RIGHT(Year@row, 3) = "MAY", "05 May", IF(RIGHT(Year@row, 3) = "JUN", "06 Jun", IF(RIGHT(Year@row, 3) = "JUL", "07 Jul", IF(RIGHT(Year@row, 3) = "AUG", "08 Aug", IF(RIGHT(Year@row, 3) = "SEP", "09 Sep", IF(RIGHT(Year@row, 3) = "OCT", "10 Oct", IF(RIGHT(Year@row, 3) = "NOV", "11 Nov", IF(RIGHT(Year@row, 3) = "DEC", "12 Dec"))))))))))))), IF(IFERROR(ISNUMBER(VALUE(RIGHT(Year@row, 1))), false), LEFT(Year@row, LEN(Year@row) - 5) + RIGHT(Year@row, 2) + (IF(MID(Year@row, LEN(Year@row) - 4, 3) = "JAN", "01 Jan", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "FEB", "02 Feb", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "MAR", "03 Mar", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "APR", "04 Apr", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "MAY", "05 May", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "JUN", "06 Jun", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "JUL", "07 Jul", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "AUG", "08 Aug", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "SEP", "09 Sep", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "OCT", "10 Oct", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "NOV", "11 Nov", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "DEC", "12 Dec"))))))))))))), IF(LEN(Year@row) - LEN(SUBSTITUTE(Year@row, CHAR(32), "")) = 2, LEFT(Year@row, LEN(Year@row) - FIND(CHAR(32), Year@row) - 3) + (IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "JAN", "01", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "FEB", "02", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "MAR", "03", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "APR", "04", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "MAY", "05", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "JUN", "06", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "JUL", "07", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "AUG", "08", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "SEP", "09", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "OCT", "10", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "NOV", "11", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "DEC", "12"))))))))))))) + MID(Year@row, FIND(CHAR(32), Year@row), LEN(Year@row) + 1 - FIND(CHAR(32), Year@row)), IF(FIND(CHAR(32), Year@row) > 0, LEFT(Year@row, FIND(CHAR(32), Year@row) - 3) + (IF(RIGHT(Year@row, 3) = "JAN", "01 Jan", IF(RIGHT(Year@row, 3) = "FEB", "02 Feb", IF(RIGHT(Year@row, 3) = "MAR", "03 Mar", IF(RIGHT(Year@row, 3) = "APR", "04 Apr", IF(RIGHT(Year@row, 3) = "MAY", "05 May", IF(RIGHT(Year@row, 3) = "JUN", "06 Jun", IF(RIGHT(Year@row, 3) = "JUL", "07 Jul", IF(RIGHT(Year@row, 3) = "AUG", "08 Aug", IF(RIGHT(Year@row, 3) = "SEP", "09 Sep", IF(RIGHT(Year@row, 3) = "OCT", "10 Oct", IF(RIGHT(Year@row, 3) = "NOV", "11 Nov", IF(RIGHT(Year@row, 3) = "DEC", "12 Dec"))))))))))))))))))

    Does that work for you?

    Kelly

«1

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Swetha

    Try this. The formula may look intimidating but most of it is converting the 3-letter month name into a number.

    =IF(FIND("-", Year@row) > 0, "TEST" + (MID(Year@row, FIND("-", Year@row) + 1, 2)) + (IF(RIGHT(Year@row, 3) = "JAN", "01 Jan", IF(RIGHT(Year@row, 3) = "FEB", "02 Feb", IF(RIGHT(Year@row, 3) = "MAR", "03 Mar", IF(RIGHT(Year@row, 3) = "APR", "04 Apr", IF(RIGHT(Year@row, 3) = "MAY", "05 May", IF(RIGHT(Year@row, 3) = "JUN", "06 Jun", IF(RIGHT(Year@row, 3) = "JUL", "07 Jul", IF(RIGHT(Year@row, 3) = "AUG", "08 Aug", IF(RIGHT(Year@row, 3) = "SEP", "09 Sep", IF(RIGHT(Year@row, 3) = "OCT", "10 Oct", IF(RIGHT(Year@row, 3) = "NOV", "11 Nov", IF(RIGHT(Year@row, 3) = "DEC", "12 Dec"))))))))))))), IF(ISNUMBER(VALUE(RIGHT(Year@row, 1))), "TEST" + RIGHT(Year@row, 2) + (IF(MID(Year@row, LEN(Year@row) - 4, 3) = "JAN", "01 Jan", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "FEB", "02 Feb", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "MAR", "03 Mar", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "APR", "04 Apr", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "MAY", "05 May", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "JUN", "06 Jun", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "JUL", "07 Jul", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "AUG", "08 Aug", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "SEP", "09 Sep", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "OCT", "10 Oct", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "NOV", "11 Nov", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "DEC", "12 Dec")))))))))))))))

    If your 'TEST' is going to be a different word or value, I suggest copying this formula into something like MS Word and doing a global replace of "Test" with your real word. When doing so, pay close attention to the quote signs as you copy back into Smartsheet. Make sure the quote sign do not appear slanted, but are the regular vertical quotes as you see typed here. Some text programs will use the slanted style quotes and smartsheet will produce an error.

    Will it work for you?

    Kelly

  • Swetha
    Swetha
    edited 02/11/23

    @Kelly Moore : Thanks a lot!! I really appreciate for providing this formula.. Actually, i did a small mistake in asking.

    The formula works exactly, as i needed for TEST value. However, for Value starting other than TEST i need the same name to be retained instead of changing it to TEST. For ex: SAPFEB23 should return value SAP2302 Feb ; CAP2303JUN should return value CAP2306 JUN , If it is already in the correct format it should retain the same CAP2201 Jan as Same value CAP2201 Jan so on:


    Can you please help me with this, i really need this to work?

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Swetha

    No worries. That only requires a small adjustment. The CHAR(32) in the first line is another way of designating a space. It's a personal preference - I find it easier to keep track of CHAR(32) in a long formula than enclosing the space in quotes.

    =IF(OR(LEN(Year@row) < 6, FIND(CHAR(32), Year@row) = LEN(Year@row) - 3), Year@row, IF(FIND("-", Year@row) > 0, LEFT(Year@row, FIND("-", Year@row) - 1) + (MID(Year@row, FIND("-", Year@row) + 1, 2)) + (IF(RIGHT(Year@row, 3) = "JAN", "01 Jan", IF(RIGHT(Year@row, 3) = "FEB", "02 Feb", IF(RIGHT(Year@row, 3) = "MAR", "03 Mar", IF(RIGHT(Year@row, 3) = "APR", "04 Apr", IF(RIGHT(Year@row, 3) = "MAY", "05 May", IF(RIGHT(Year@row, 3) = "JUN", "06 Jun", IF(RIGHT(Year@row, 3) = "JUL", "07 Jul", IF(RIGHT(Year@row, 3) = "AUG", "08 Aug", IF(RIGHT(Year@row, 3) = "SEP", "09 Sep", IF(RIGHT(Year@row, 3) = "OCT", "10 Oct", IF(RIGHT(Year@row, 3) = "NOV", "11 Nov", IF(RIGHT(Year@row, 3) = "DEC", "12 Dec"))))))))))))), IF(ISNUMBER(VALUE(RIGHT(Year@row, 1))), LEFT(Year@row, LEN(Year@row) - 5) + RIGHT(Year@row, 2) + (IF(MID(Year@row, LEN(Year@row) - 4, 3) = "JAN", "01 Jan", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "FEB", "02 Feb", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "MAR", "03 Mar", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "APR", "04 Apr", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "MAY", "05 May", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "JUN", "06 Jun", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "JUL", "07 Jul", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "AUG", "08 Aug", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "SEP", "09 Sep", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "OCT", "10 Oct", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "NOV", "11 Nov", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "DEC", "12 Dec"))))))))))))))))

    Will this work for you?

    Kelly

  • @Kelly Moore : Thanks a lot!! It works. However, if a value as CL2303 JUN MAC, i need it to convert according to month name as CL2306 JUN MAC.

    I tried to came up with formula as:

    =IF(AND(CONTAINS("CL", [Year]@row), CONTAINS("Jan", Year]@row)), REPLACE([Year]@row, 5, 2, "01"), IF(AND(CONTAINS("CL", [Year]@row), CONTAINS("Apr", [Year]@row)), REPLACE([Year]@row@row, 5, 2, "04"), IF(AND(CONTAINS("CL", [Year]@row), CONTAINS("Jul", [Year]@row), REPLACE([Year]@row, 5, 2, "07"), IF(AND(CONTAINS("CL", [Year]@row), CONTAINS("Aug", [Year]@row), REPLACE([Year]@row, 5, 2, "08"), IF(AND(CONTAINS("CL", [Year]@row, CONTAINS("Oct", [Year]@row), REPLACE([Year]@row, 5, 2, "10"), [Year]@row)))))

    But not to sure how to append this conversion for the formula which you provided, i need this logic too. Could you please help me on this?

    I have provided the example, please look at the attached highlighted values:


  • @Kelly Moore : Could you please take a look at the above attached screenshot and i need to include that logic in the formula which you provided.

    please can you add this logic too?

  • @Kelly Moore : I have been posting this scenario couple of times asking different variations. Writing everything again. Could you please help me with this.

    I have column "Year" with different variations (some text with -) and year + month values.. I need formula where value to be return in "Target" column

    1. By substituting hyphen(-) with year & month value. Ex: TEST-23AUG to TEST2308 AUG
    2. If there is no hyphen for TEST value it needs to be returned same. Ex: TESTFEB23 to TESTFEB23
    3. For other variation it should be replaced with same exact month number. Ex: AL2202 Apr MPR to AL2204 Apr MPR, AL2203 Aug MPR to AL2208 Aug MPR etc..
    4. If it is already having exact month it should return the same value. Ex: DL2202 Feb to DL2202 FebOL2010 Oct to OL2010 Oct
    5. If there are values NA, TBD, Prod it should retain same: NA, TBD, Prod

    or Scenario point 3 to point 5 it works with the below formula:

    =IF(AND(CONTAINS("CL", Year@row), CONTAINS("Jan", Year@row)), REPLACE(Year@row, 5, 2, "01"), IF(AND(CONTAINS("CL", Year@row), CONTAINS("Apr", Year@row)), REPLACE(Year@row, 5, 2, "04"), IF(AND(CONTAINS("CL", Year@row), CONTAINS("Jul", Year@row)), REPLACE(Year@row, 5, 2, "07"), IF(AND(CONTAINS("CL", Year@row), CONTAINS("Aug", Year@row)), REPLACE(Year@row, 5, 2, "08"), IF(AND(CONTAINS("CL", Year@row), CONTAINS("Oct", Year@row)), REPLACE(Year@row, 5, 2, "10"), Year@row)))))

    For Scenario point 1 below formula works. However for point 2, the formula is returning the value as Test2302FEB. I just need the same value to be returned as TESTFEB23 instead of changing.

    =IF(FIND("-", Year@row) > 0, "TEST" + (MID(Year@row, FIND("-", Year@row) + 1, 2)) + (IF(RIGHT(Year@row, 3) = "JAN", "01 Jan", IF(RIGHT(Year@row, 3) = "FEB", "02 Feb", IF(RIGHT(Year@row, 3) = "MAR", "03 Mar", IF(RIGHT(Year@row, 3) = "APR", "04 Apr", IF(RIGHT(Year@row, 3) = "MAY", "05 May", IF(RIGHT(Year@row, 3) = "JUN", "06 Jun", IF(RIGHT(Year@row, 3) = "JUL", "07 Jul", IF(RIGHT(Year@row, 3) = "AUG", "08 Aug", IF(RIGHT(Year@row, 3) = "SEP", "09 Sep", IF(RIGHT(Year@row, 3) = "OCT", "10 Oct", IF(RIGHT(Year@row, 3) = "NOV", "11 Nov", IF(RIGHT(Year@row, 3) = "DEC", "12 Dec"))))))))))))), IF(ISNUMBER(VALUE(RIGHT(Year@row, 1))), "TEST" + RIGHT(Year@row, 2) + (IF(MID(Year@row, LEN(Year@row) - 4, 3) = "JAN", "01 Jan", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "FEB", "02 Feb", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "MAR", "03 Mar", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "APR", "04 Apr", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "MAY", "05 May", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "JUN", "06 Jun", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "JUL", "07 Jul", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "AUG", "08 Aug", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "SEP", "09 Sep", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "OCT", "10 Oct", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "NOV", "11 Nov", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "DEC", "12 Dec")))))))))))))))

    I need a single formula which works from Point 1 to Point 5. Can any one of you, please help me on this.


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 02/15/23

    Hey @Swetha

    I saw your other posts. The issue is the inconsistency of your data entry. You will continually have these exceptions until you standardize the data entry. Hopefully you have addressed this for any future entries.

    I'll work on the above tonight. And are we now ignoring the CL data you posted above this colored table?

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Swetha

    try this

    =IF(LEN(Year@row) < 6, Year@row, IF(LEN(Year@row) - LEN(SUBSTITUTE(Year@row, CHAR(32), "")) = 2, LEFT(Year@row, LEN(Year@row) - FIND(CHAR(32), Year@row) - 3) + (IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "JAN", "01", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "FEB", "02", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "MAR", "03", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "APR", "04", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "MAY", "05", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "JUN", "06", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "JUL", "07", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "AUG", "08", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "SEP", "09", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "OCT", "10", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "NOV", "11", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "DEC", "12"))))))))))))) + MID(Year@row, FIND(CHAR(32), Year@row), LEN(Year@row) + 1 - FIND(CHAR(32), Year@row)), IF(FIND(CHAR(32), Year@row) > 0, LEFT(Year@row, FIND(CHAR(32), Year@row) - 3) + (IF(RIGHT(Year@row, 3) = "JAN", "01 Jan", IF(RIGHT(Year@row, 3) = "FEB", "02 Feb", IF(RIGHT(Year@row, 3) = "MAR", "03 Mar", IF(RIGHT(Year@row, 3) = "APR", "04 Apr", IF(RIGHT(Year@row, 3) = "MAY", "05 May", IF(RIGHT(Year@row, 3) = "JUN", "06 Jun", IF(RIGHT(Year@row, 3) = "JUL", "07 Jul", IF(RIGHT(Year@row, 3) = "AUG", "08 Aug", IF(RIGHT(Year@row, 3) = "SEP", "09 Sep", IF(RIGHT(Year@row, 3) = "OCT", "10 Oct", IF(RIGHT(Year@row, 3) = "NOV", "11 Nov", IF(RIGHT(Year@row, 3) = "DEC", "12 Dec"))))))))))))), IF(FIND("-", Year@row) > 0, LEFT(Year@row, FIND("-", Year@row) - 1) + (MID(Year@row, FIND("-", Year@row) + 1, 2)) + (IF(RIGHT(Year@row, 3) = "JAN", "01 Jan", IF(RIGHT(Year@row, 3) = "FEB", "02 Feb", IF(RIGHT(Year@row, 3) = "MAR", "03 Mar", IF(RIGHT(Year@row, 3) = "APR", "04 Apr", IF(RIGHT(Year@row, 3) = "MAY", "05 May", IF(RIGHT(Year@row, 3) = "JUN", "06 Jun", IF(RIGHT(Year@row, 3) = "JUL", "07 Jul", IF(RIGHT(Year@row, 3) = "AUG", "08 Aug", IF(RIGHT(Year@row, 3) = "SEP", "09 Sep", IF(RIGHT(Year@row, 3) = "OCT", "10 Oct", IF(RIGHT(Year@row, 3) = "NOV", "11 Nov", IF(RIGHT(Year@row, 3) = "DEC", "12 Dec"))))))))))))), IF(ISNUMBER(VALUE(RIGHT(Year@row, 1))), LEFT(Year@row, LEN(Year@row) - 5) + RIGHT(Year@row, 2) + (IF(MID(Year@row, LEN(Year@row) - 4, 3) = "JAN", "01 Jan", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "FEB", "02 Feb", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "MAR", "03 Mar", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "APR", "04 Apr", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "MAY", "05 May", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "JUN", "06 Jun", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "JUL", "07 Jul", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "AUG", "08 Aug", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "SEP", "09 Sep", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "OCT", "10 Oct", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "NOV", "11 Nov", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "DEC", "12 Dec"))))))))))))))))))

  • @Kelly Moore :

    I'm really thankful for taking your time to work on this formula. It is working for all the scenarios starting from AL,OL,NA,CL & TEST.

    From the above scenario, 1,3,4,5 are working fine except for 2.

    For TEST, if it is having hyphen then only it should append with Month number. If the TEST does not contain hypen it should remain the same. However with the formula even it is getting converted.

    1. By substituting hyphen(-) with year & month value. Ex: TEST-23AUG to TEST2308 AUG
    2. If there is no hyphen for TEST value it needs to be returned same. Ex: TESTFEB23 to TESTFEB2

    As per the current formula; TESTFEB23 is converted to TEST2302 FEB, i dont need this conversion for this format or not having hyphen. I just need this conversion for 1,3,4 points.

    Is it possible to have this included for the TEST which is not having hypen or the format which is little different compare to 1st example. Can you please take a look.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey

    I'll look again in the next few evenings. I cannot stress enough that if formats are changing, the formula will not adjust. It is not intuitive - it does exactly what is programmed. A cell is also limited to 4,000 characters. There will come a point where you cannot adjust to the exceptions using the months embedded within the formula. We would have to use a different approach to pull in the right month number.

    Kelly

  • @Kelly Moore : I understand!!! Its just these formats. Sure, take your time... Thanks a lot!!

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

    Hey @Swetha

    See if this satisfies your conditions

    =IF(OR(LEN(Year@row) < 6, AND(IFERROR(ISNUMBER(VALUE(RIGHT(Year@row, 1))), false), LEFT(Year@row, 4) = "TEST")), Year@row, IF(FIND("-", Year@row) > 0, LEFT(Year@row, FIND("-", Year@row) - 1) + (MID(Year@row, FIND("-", Year@row) + 1, 2)) + (IF(RIGHT(Year@row, 3) = "JAN", "01 Jan", IF(RIGHT(Year@row, 3) = "FEB", "02 Feb", IF(RIGHT(Year@row, 3) = "MAR", "03 Mar", IF(RIGHT(Year@row, 3) = "APR", "04 Apr", IF(RIGHT(Year@row, 3) = "MAY", "05 May", IF(RIGHT(Year@row, 3) = "JUN", "06 Jun", IF(RIGHT(Year@row, 3) = "JUL", "07 Jul", IF(RIGHT(Year@row, 3) = "AUG", "08 Aug", IF(RIGHT(Year@row, 3) = "SEP", "09 Sep", IF(RIGHT(Year@row, 3) = "OCT", "10 Oct", IF(RIGHT(Year@row, 3) = "NOV", "11 Nov", IF(RIGHT(Year@row, 3) = "DEC", "12 Dec"))))))))))))), IF(IFERROR(ISNUMBER(VALUE(RIGHT(Year@row, 1))), false), LEFT(Year@row, LEN(Year@row) - 5) + RIGHT(Year@row, 2) + (IF(MID(Year@row, LEN(Year@row) - 4, 3) = "JAN", "01 Jan", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "FEB", "02 Feb", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "MAR", "03 Mar", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "APR", "04 Apr", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "MAY", "05 May", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "JUN", "06 Jun", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "JUL", "07 Jul", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "AUG", "08 Aug", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "SEP", "09 Sep", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "OCT", "10 Oct", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "NOV", "11 Nov", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "DEC", "12 Dec"))))))))))))), IF(LEN(Year@row) - LEN(SUBSTITUTE(Year@row, CHAR(32), "")) = 2, LEFT(Year@row, LEN(Year@row) - FIND(CHAR(32), Year@row) - 3) + (IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "JAN", "01", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "FEB", "02", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "MAR", "03", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "APR", "04", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "MAY", "05", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "JUN", "06", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "JUL", "07", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "AUG", "08", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "SEP", "09", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "OCT", "10", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "NOV", "11", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "DEC", "12"))))))))))))) + MID(Year@row, FIND(CHAR(32), Year@row), LEN(Year@row) + 1 - FIND(CHAR(32), Year@row)), IF(FIND(CHAR(32), Year@row) > 0, LEFT(Year@row, FIND(CHAR(32), Year@row) - 3) + (IF(RIGHT(Year@row, 3) = "JAN", "01 Jan", IF(RIGHT(Year@row, 3) = "FEB", "02 Feb", IF(RIGHT(Year@row, 3) = "MAR", "03 Mar", IF(RIGHT(Year@row, 3) = "APR", "04 Apr", IF(RIGHT(Year@row, 3) = "MAY", "05 May", IF(RIGHT(Year@row, 3) = "JUN", "06 Jun", IF(RIGHT(Year@row, 3) = "JUL", "07 Jul", IF(RIGHT(Year@row, 3) = "AUG", "08 Aug", IF(RIGHT(Year@row, 3) = "SEP", "09 Sep", IF(RIGHT(Year@row, 3) = "OCT", "10 Oct", IF(RIGHT(Year@row, 3) = "NOV", "11 Nov", IF(RIGHT(Year@row, 3) = "DEC", "12 Dec"))))))))))))))))))

    Does that work for you?

    Kelly

  • @Kelly Moore : Thank you, it works... You are an angel :) Thank you very much !!!!

  • @Kelly Moore : For the below formula, i also need to include 2 more values starting from MAGI-YYMMM and MAGR-YYMMM. However, there is a twist in this, if year starts from 23 and higher then only it needs to be substituted with year and month to display.

    Ex: MAGI-23JAN = MAGI2301 JAN, MAGR-25DEC, MAGR2512 etc.... Also, the conversion should happen for year 23 and above. lesser than 23, exact value needs to display. There are other values starting from MAGI and MAGR without hypen i.e. MAGIAPR23, MAGPMAR24 these values should not convert. Only values with hypen and 23 and above YY needs to be converted.

    I tried changing, but it is coming Invalid. Could you please take a look to add to this existing formula. If we cant add this to the existing formula, can you help me with formula just only for mentioned scenario. That also works for me.

    =IF(OR(LEN(Year@row) < 6, AND(IFERROR(ISNUMBER(VALUE(RIGHT(Year@row, 1))), false), LEFT(Year@row, 4) = "TEST")), Year@row, IF(FIND("-", Year@row) > 0, LEFT(Year@row, FIND("-", Year@row) - 1) + (MID(Year@row, FIND("-", Year@row) + 1, 2)) + (IF(RIGHT(Year@row, 3) = "JAN", "01 Jan", IF(RIGHT(Year@row, 3) = "FEB", "02 Feb", IF(RIGHT(Year@row, 3) = "MAR", "03 Mar", IF(RIGHT(Year@row, 3) = "APR", "04 Apr", IF(RIGHT(Year@row, 3) = "MAY", "05 May", IF(RIGHT(Year@row, 3) = "JUN", "06 Jun", IF(RIGHT(Year@row, 3) = "JUL", "07 Jul", IF(RIGHT(Year@row, 3) = "AUG", "08 Aug", IF(RIGHT(Year@row, 3) = "SEP", "09 Sep", IF(RIGHT(Year@row, 3) = "OCT", "10 Oct", IF(RIGHT(Year@row, 3) = "NOV", "11 Nov", IF(RIGHT(Year@row, 3) = "DEC", "12 Dec"))))))))))))), IF(IFERROR(ISNUMBER(VALUE(RIGHT(Year@row, 1))), false), LEFT(Year@row, LEN(Year@row) - 5) + RIGHT(Year@row, 2) + (IF(MID(Year@row, LEN(Year@row) - 4, 3) = "JAN", "01 Jan", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "FEB", "02 Feb", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "MAR", "03 Mar", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "APR", "04 Apr", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "MAY", "05 May", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "JUN", "06 Jun", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "JUL", "07 Jul", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "AUG", "08 Aug", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "SEP", "09 Sep", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "OCT", "10 Oct", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "NOV", "11 Nov", IF(MID(Year@row, LEN(Year@row) - 4, 3) = "DEC", "12 Dec"))))))))))))), IF(LEN(Year@row) - LEN(SUBSTITUTE(Year@row, CHAR(32), "")) = 2, LEFT(Year@row, LEN(Year@row) - FIND(CHAR(32), Year@row) - 3) + (IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "JAN", "01", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "FEB", "02", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "MAR", "03", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "APR", "04", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "MAY", "05", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "JUN", "06", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "JUL", "07", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "AUG", "08", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "SEP", "09", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "OCT", "10", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "NOV", "11", IF(MID(Year@row, FIND(CHAR(32), Year@row) + 1, 3) = "DEC", "12"))))))))))))) + MID(Year@row, FIND(CHAR(32), Year@row), LEN(Year@row) + 1 - FIND(CHAR(32), Year@row)), IF(FIND(CHAR(32), Year@row) > 0, LEFT(Year@row, FIND(CHAR(32), Year@row) - 3) + (IF(RIGHT(Year@row, 3) = "JAN", "01 Jan", IF(RIGHT(Year@row, 3) = "FEB", "02 Feb", IF(RIGHT(Year@row, 3) = "MAR", "03 Mar", IF(RIGHT(Year@row, 3) = "APR", "04 Apr", IF(RIGHT(Year@row, 3) = "MAY", "05 May", IF(RIGHT(Year@row, 3) = "JUN", "06 Jun", IF(RIGHT(Year@row, 3) = "JUL", "07 Jul", IF(RIGHT(Year@row, 3) = "AUG", "08 Aug", IF(RIGHT(Year@row, 3) = "SEP", "09 Sep", IF(RIGHT(Year@row, 3) = "OCT", "10 Oct", IF(RIGHT(Year@row, 3) = "NOV", "11 Nov", IF(RIGHT(Year@row, 3) = "DEC", "12 Dec"))))))))))))))))))

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Swetha

    As mentioned earlier, you will need another approach if you want to continue to add criteria to the formula as it is quickly approaching the 4000 character limit. A separate sheet that would act as a lookup table to convert your Month abbreviation into month numbers is needed. This would be needed before proceeding to add on - and the formula will need a complete overhaul.

    If you want a separate formula, or the overhaul, I can do that over the weekend. Let me know which approach you prefer

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!