Need help returning the right numbers in a sequence.

The Company I work for uses a Julian Date code for our product. The Formula was working until getting to the first 4 months. The formula I am using is:

=IF([Julian Day/Month]@row >= 335, "December", IF([Julian Day/Month]@row >= 305, "November", IF([Julian Day/Month]@row >= 274, "October", IF([Julian Day/Month]@row >= 244, "September", IF([Julian Day/Month]@row >= 213, "August", IF([Julian Day/Month]@row >= 182, "July", IF([Julian Day/Month]@row >= 152, "June", IF([Julian Day/Month]@row >= 121, "May", IF([Julian Day/Month]@row >= 91, "April", IF([Julian Day/Month]@row >= 60, "March", IF([Julian Day/Month]@row >= 32, " February", IF([Julian Day/Month]@row >= 1, "January", "Incorrect Date"))))))))))))

The problem I am running into is Jan-April won't return the correct month, our date code is set up like this 001227; first 3 digits are the day/month, next 2 numbers are the year followed by which machine it was packaged. With the date code being 001227 it will display Incorrect date but if the date code reads 100227 it will display January.

Is there an easier way to go about this or is my formula just not correct?

Best Answers

  • Paul H
    Paul H ✭✭✭✭✭✭
    edited 04/07/22 Answer ✓

    It is probably the leading zeros make it text not a number

    I used the value function extract the first three digits, paste in your formula to the month column and it seems to work.



  • LETCRyan
    LETCRyan
    edited 04/08/22 Answer ✓

    Awesome! Thank you! A few of us were wondering if it maybe in the Julian Day/Month formula as it was reading the left most value. After I posted this another coworker came in and asked him to take a look at it. I gave him our input on it as well, he started adding "INT" and "VALVE" into a few different spots. He did end up with the same results as you. It just dawned on me that I reached out in forestation and figured I'd remove it or check it as completed etc. Much appreciated Paul, as in the AM you would have been my hero!

Answers

  • Paul H
    Paul H ✭✭✭✭✭✭
    edited 04/07/22 Answer ✓

    It is probably the leading zeros make it text not a number

    I used the value function extract the first three digits, paste in your formula to the month column and it seems to work.



  • LETCRyan
    LETCRyan
    edited 04/08/22 Answer ✓

    Awesome! Thank you! A few of us were wondering if it maybe in the Julian Day/Month formula as it was reading the left most value. After I posted this another coworker came in and asked him to take a look at it. I gave him our input on it as well, he started adding "INT" and "VALVE" into a few different spots. He did end up with the same results as you. It just dawned on me that I reached out in forestation and figured I'd remove it or check it as completed etc. Much appreciated Paul, as in the AM you would have been my hero!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!