Need a fix to formula to verify with system date and return a value
With the current formula i'm able to verify system date with the value started with two characters but not more than 2 Characters. Can some one please help to fix this.
=IFERROR(IF(VALUE(MID(SUBSTITUTE(Source@row, " ", ""), 3, 2)) > VALUE(RIGHT(YEAR(TODAY()), 2)), "N/A", IF(AND(VALUE(MID(SUBSTITUTE(Source@row, " ", ""), 3, 2)) = VALUE(RIGHT(YEAR(TODAY()), 2)), VALUE(MID(SUBSTITUTE(Source@row, " ", ""), 5, 2)) > VALUE(MONTH(TODAY()))), "N/A", Source@row)), Source@row)
Ex: I need values to be displayed as in the Required column in the attached pic. However, it is displayed as in the Error column.(Just the highlighted values are not working correctly, remaining works )
I have column "Source" with text values(some text with numbers too) and date values.. I need text value to be return as it is in "Target" column. However, for the date values, it need to compare with System date.
If year is greater than the current year and equal to current year but month is equal to or greater then current month value return should be "N/A".
If the year is less than current year and equal to current year but less than current month, exact value from source column should be returned.
Also, the values which does not have date format, need to return the exact source column value.
Answers

Instead of =VALUE(MID(SUBSTITUTE(Source@row, " ", ""), 3, 2))
just pull the 8 characters from the right, then use LEFT to get the first two characters from the left of the result:
=VALUE(LEFT(RIGHT(Source@row, 8), 2))
This way it doesn't matter how many alpha characters precede the year digits. LANE2309 FEB returns a helper value of 23. AL2304 APR returns a value of 23.
You can also drop the SUBSTITUTE, you don't need it.
You can also do the same thing for the month value, but just pull the 6 spaces from the right:
=VALUE(LEFT(RIGHT(Source@row, 6), 2))
So here's the whole thing, simplified:
=IFERROR(IF(VALUE(LEFT(RIGHT(Source@row, 8), 2)) > VALUE(RIGHT(YEAR(TODAY()), 2)), "N/A", IF(AND(VALUE(LEFT(RIGHT(Source@row, 8), 2)) = VALUE(RIGHT(YEAR(TODAY()), 2)), VALUE(LEFT(RIGHT(Source@row, 6), 2)) > VALUE(MONTH(TODAY()))), "N/A", Source@row)), Source@row)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

@Jeff Reisman : Thanks for helping with the formula. Also, i have a data variation for example;
In this case if i pull the 8 characters, logic does not work.
Is there a possibility we can include this variation too in formula? Along, with AL2306 JUN,LANE2302 FEB, N/A,Prod,OL2202 FEB.
Could you please take a look?

@Swetha With so many variations in your data, I would recommend working on trimming the data down into a consistent format for data extraction. There's only so much you can do before a formula becomes too long to manage effectively. Either that, or use something like the LEN function to get the length of the data value, and use that length to specify which formula to use in a series of nested IFs. For instance:
=IF(LEN(Source@row) = 14, place a formula here, IF(LEN(Source@row) = 10, place a formula here, IF(LEN(Source@row) = 13, place a formula here, Source@row)))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!