Conditional Format with multiple dates in one cell

Stephen.R

New Member
Joined
Jul 26, 2012
Messages
13
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
[TABLE="width: 500"]
<tbody>[TR]
[TD]A = 01/04/2019, 13/03/2020, 13/03/2020, M = 06/12/2019[/TD]
[/TR]
[TR]
[TD]26/10/2018[/TD]
[/TR]
[TR]
[TD]A = 26/10/2018[/TD]
[/TR]
</tbody>[/TABLE]

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()"
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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?
 
Last edited:
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Some have just 1 date, some multiple dates, some show A and or M etc. - please see below an example of the different formats:

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

These are all the non date options that the column has listed.
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0
OK, try this code. It will highlight any cells in column A that contain a past date.
Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top