Need a fix to formula to verify with system date and return a value

Swetha

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.

Jeff Reisman

@Swetha

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)

Swetha

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

Jeff Reisman

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