Conditional Format with multiple dates in one cell

[Stephen.R]

Hi All,

I am trying to (easy part) conditional format a column of dates to highlight past dates.
<today()"
<today().
Easy enough when they contain just one date, however some contain multiple dates - e.g.
Column A

A = 01/04/2019, 13/03/2020, 13/03/2020, M = 06/12/2019

26/10/2018

A = 26/10/2018

<tbody>
</tbody>

Is it possible without splitting out all the dates into subsequent columns?

I assume it involves a wildcard feature to look for the date within but I cannot get it to work.
Any help would be appreciated.
P.S. I am on Office 365</today().
</today()"

 

[Joe4]

The issue is when you have multiple dates in a single cell, the values cease to be dates and the whole thing is treated as a string.
The only way I can really think to do this is using VBA.

Are you open to a VBA solution?

 

[Stephen.R]

Oh right.
Yes please, I've used VBA and macros before, even wrote a few simple ones but haven't used them in too long.

 

[Joe4]

So, do some of these cells have values other than dates in them, like the "A" and "M" you show in your examples?
Can you post all the different types of format those entries in column A may look like, so we can be sure that we account for all the different structures?

 

[Stephen.R]

Some have just 1 date, some multiple dates, some show A and or M etc. - please see below an example of the different formats:

26/10/2020	Can be just 1 date
02/05/2020, 19/12/2021, 02/08/2019, 19/04/2023, 19/04/2023	multiple dates
M = 05/02/2020	M =
A = 26/05/2020, M = 03/11/2020	A = and M =
A = 16/05/2020, M = 16/11/2019, 31/07/2021, 31/07/2021	More Dates for M =
A = 01/04/2019, 13/03/2020, 13/03/2020, M = 06/12/2019	More Dates for A =
A = 11/09/2019, M = ?, 30/04/2020	Could have ?
A = 22/03/2019, M = N/A	Could have N/A
A = N, M = 05/07/2019	Could have N
A = N/A, M = 27/04/2020, 11/07/2020	Could have N/A
A = ?, M = 03/01/2020	Could have ?
Shop = N/A, M = 20/03/2020, 29/02/2020	Could have Shop
MHF A = 19/07/2019, CS A = N/A, M = 09/12/2020	Could have MHF and CS

<tbody>
</tbody>

These are all the non date options that the column has listed.

 

[Stephen.R]

Yes they do. Options are: single date, multiple dates, Single A date but multiple M dates or vice versa
Also they may include: N/A, N, ?, MHF A, CS A, Shop

e.g.
02/05/2020
19/12/2021, 02/08/2019, 19/04/2023, 19/04/2023
M = 05/02/2020
A = 05/02/2020
MHF A = 19/07/2019, CS A = N/A, M = 09/12/2020
Shop = N/A, A = N, M = 02/07/2019
A = ?, M = 03/01/2020
A = 25/05/2020, 04/02/2023, M = 18/08/2019, 13/06/2023

Thank you

 

[Joe4]

Wow, the format of this data is all over the place. It could take me a little whole to come up with something.

One last question, are there any case in which you would have a comma that is NOT immediately followed by a space?
From your examples, it looks like the commas are always followed by spaces (which would be helpful to me if this was always the case).

 

[Stephen.R]

Yeah, and if it was just my spreadsheet I would separate the out the dates.

Commas always follow spaces and I have made sure it is 1 space only etc.

Thank you

 

[Joe4]

OK, try this code. It will highlight any cells in column A that contain a past date.

Sub DateCheck()

    Dim rng As Range
    Dim cell As Range
    Dim arr() As String
    Dim i As Long
    Dim substr As String
    Dim sdate As Date
    
    Application.ScreenUpdating = False
    
'   Set range to apply to (all populated rows in column A)
    Set rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        
'   Loop through all cells in column A
    For Each cell In rng
'       Remove commas, and split values based on spaces
        arr = Split(Replace(cell, ",", ""), " ")
    
'       Loop through each split value
        For i = LBound(arr) To UBound(arr)
            substr = arr(i)
'           Check to see if this looks like a date, and convert it to a date
            If Len(substr) = 10 And Mid(substr, 3, 1) = "/" And Mid(substr, 6, 1) = "/" Then
                sdate = DateValue(substr)
'               If date is less than current date, highlight cell and move to next cell
                If sdate < Date Then
                    cell.Interior.Color = 65535
                    Exit For
                End If
            End If
        Next i
    Next cell
    
    Application.ScreenUpdating = True
    
    MsgBox "Done!"
    
End Sub

 

[Stephen.R]

Wow, thank you so much, worked like a dream.
I did change the column to M and the colour code to red (255) and still works.

Thank you again