Detecting Date In Test String

[Newbienew]

I am trying to use conditional formatting the formula part to detect the todays date with in the text string. All entries that are made start with the todays date, and due to it being so many entries that are placed every day it is a hard to sort thru all the new entries with the filter. So the idea is to highlight those entries for that day and then be able to sort by color.

I was thinking the code was =if(today()&text.... formula but that did not work out for me. Please help

 

[FormR]

detect the todays date with in the text string.

Hi, can you post a few representative examples of the text strings that contain the date?

 

[Newbienew]

4/11/2019 Member is looking to relocate
4/11/2019 Member has to report to RM 5 on the 18th floor
4/11/2019 Member quitting for new job
4/10/2019 Member has transfer position docs signed and submitted

So each one of these entries is for a different person but being today's date in in the first 3 cells it would be highlighted regardless of what is to follow after the date. But if today would be the 4/12/2019 the dates would not be highlighted any more but the

4/12/2019 would be highlighted as the entry for that date.

 

[PaddyD]

Can you be confident that the dates are always entered in the same format? e.g. could the 1st April 2019 be any of 4/1/2019, 04/01/2019, 4/01/19 etc? If there is a fixed date format, what is it?

 

[Rick Rothstein]

Give this Conditional Formatting formula a try...

=LEFT(A1,FIND(" ",A1&" ")-1)+0=TODAY()

Note: A1 is assumed to be the active cell in the selection you are applying the Conditional Formatting to.

 

[Newbienew]

@ PaddyD That is a great question, it is normally written 4/11/2019 but there have been slight differences.

@ Rick Rothstein This works amazingly great. Based off PaddyD question there are some differences in what is after the date. Such as : and ,

I thought it would be a OR function but don't know where to but the : or , for it to be counted as well.

Thank you both for the speedy response.

 

[Rick Rothstein]

@ Rick Rothstein This works amazingly great. Based off PaddyD question there are some differences in what is after the date. Such as : and ,

I thought it would be a OR function but don't know where to but the : or , for it to be counted as well.

Okay, give this a try then...

=LEFT(A1,FIND(" ",SUBSTITUTE(SUBSTITUTE(A1,":"," "),","," ")&" ")-1)+0=TODAY()

 

[Newbienew]

THANK YOU SOOO MUCH. Works grrrrreeat.

I have additional question. Not sure if i should start a new thread or not.

But

I am looking for a conditional format formula that if a date is entered, which is normally entered 4/12/2019 dont believe it will change but dont sure, but i need it to be highlighted if its goes over 5 days but it can only count the work week not the weekend. I did =if(a1=today()+5,"true","") but i think that is wrong. Thankyou in advance

 

[FormR]

Hi, for your new question you can try:

=A1>=WORKDAY(TODAY(),5)

Not sure if i should start a new thread or not.

It's generally better to start new threads for new questions.

 

[Newbienew]

I just learned in another thread I need to be extra detailed in my questions and replies. lol
I used the formula but when I did everything turned red for me. Red being the highlighted color of choice.
It didnt matter if I entered a test entries as I put a date in 5 days ago and one 6 days ago.
Please let me know if you need more info or so. thank you gain for you help