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