Date Issue

[Front]

I thought I had this one, but after testing there is one situation that fails. I inserted this in my conditional formatting.

=AND(Y3<$AD$36,ISBLANK(P3),Y3<>"")

AD36 will have a two digit date and then the word “Close” after it. I want the formula to return true if Y3 has a date in it that is before the date in AD36. Y3 will likely also have other words.

P3 must be empty and Y3 cannot be empty to be true.

The above formula worked fine, I thought it was good, but it failed if there is ONLY a date in Y3 whiteout other words. If Y3 has a date and words it works perfect. If its only a date it returns true even if the date in Y3 comes after AD36, which really does not make sense to me.

Can anyone advise on how to fix?

 

[Toadstool]

Hi Front,

I'm not sure what a two digit date is, just the year? If so then does this work

=AND(LEFT(Y3,2)<LEFT($AD$36,2),ISBLANK(P3),Y3<>"")

 

[Front]

Thank you. Sorry for not being clear. by two digit date I mean without the year. So 11/6. This is the exact code that is in AD36

=(TEXT(TODAY(),"m/d")&" Close")

 

[Toadstool]

It is unfortunate you use m/d format as that means November comes before April, as the 1 of 11 is lower than the 4. As it will always have a word suffix then it will always be text so you can create a date.

You say Y3 may have words suffixed but if only numbers are entered does it default to a date or does it remain text? You can click on Y3 and see if it just has a number or a date with a year.

If Y3 may be either a text field with the m/d suffix or just a date field then this should work (but always assume both dates are in the current year)

=IF(ISNUMBER(Y3),AND(Y3<(YEAR(TODAY())&"/"&LEFT(AD36,FIND(" ",AD36)-1))+0,ISBLANK(P3),Y3<>""),AND(((YEAR(TODAY())&"/"&LEFT(Y3,FIND(" ",Y3)-1))+0)<(YEAR(TODAY())&"/"&LEFT(AD36,FIND(" ",AD36)-1))+0,ISBLANK(P3),Y3<>""))

 

[Front]

Thank you. That did not work, but considering what you said about November coming before April, I could switch to a full date.

=(TEXT(TODAY(),"m/d/y")&" Close")

Y3 has something in it like “11/05 Close First”, but I could change to “11/05/21 Close First” if that makes a difference.

 

[Toadstool]

Having the complete date in both Y3 and AAD36 makes it easier.

Try this:

=AND((LEFT(Y3&" ",FIND(" ",Y3&" ")-1)+0)<(LEFT($AD$36&" ",FIND(" ",$AD$36&" ")-1)+0),ISBLANK(P3),Y3<>"")

 

[Front]

Thank you so much! Worked!

 

[Toadstool]

You're welcome.