Macro to highlight cells not containing valid dates

Lucas in London

Board Regular
Joined
Jun 17, 2002
Messages
88
Hi,

This is pretty strightforward one, hope someone out there can share their wisdom.

I need a procedure that will loop through all populated cells in say row 5 of a spreadsheet and highlight (e.g. change font colour red) all entries that do not represent genuine dates. Underlying dates are currently of the format: dd/mm/yyyy. I want the macro to find entries that are not recognised as dates by excel.

Thanks

Luke
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
Here's a simple routine- just highlight the cells you want to check and run the macro-

Code:
Public Sub CheckDates()
Dim c As Range

For Each c In Selection
    If Not IsDate(c.Value) And c.Value <> vbNullString Then
        c.Font.ColorIndex = 3
    End If
Next c

End Sub
 

Lucas in London

Board Regular
Joined
Jun 17, 2002
Messages
88
Re: Macro to highlight cells not containing valid dates/time

Mudface (I presume this is a nickname!),

Thanks for the code and your suggestions. I've tested out the macro on a few examples and in general it does seem to work but is not in all instances.

For example, on an entry such as '24-feb-2004, the macro classifies this as a valid date even though it contains an apostrophe character in it. Another example is 12-04/01. This is not a valid date yet the macro ignores it.

So is there a way for it to highlight all cells that do not conform with the following date format: dd/mmm/YY (e.g. 03-Mar-04)?

Also can you apply similar code to identify invalid times, ie’ all times not in the underlying format of HH:MM:SS?

Thanks,

Lucas
 

Lucas in London

Board Regular
Joined
Jun 17, 2002
Messages
88
Hello,

Anyone out there with any solutions/suggestion for my problem?

Thanks,

Lucas (In London, England, Great Britain)
 

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
723
It would appear you need to clarify what a valid date format is since the ones that you indicate are being "ignored" are in fact valid dates to excel. The information within the cell can be translated to a valid date, thus ISDATE returns it as true. If you are looking to take the information from these cells and change the format to dd-mmm-YY then that is a different question.


Other questions to be answered would be, are your cells formatted as DATE or General? Can you enter a date into any cell and is this the data you want to have conform to a valid date, a valid date format or both?
 

Lucas in London

Board Regular
Joined
Jun 17, 2002
Messages
88
Hello Again,

In the spreadsheet taht I want to apply the checks to, all the cells containing are formatted as dates. But some of these cells (dates) are populated by copying copied dates from other spreadsheets where some are formatted as General.

The Isdate function appears to work in all instances except where an apostrophe proceeds the date, i.e. '10-MAR-2004. To see if this any implications, I tried sorting a series of dates with the instance of one containing an apostrophe this but it places it at the bottom of the list as per below.

09-Mar-04
10-Mar-04 (date without apostrophe)
15-Mar-04
01-Apr-04
10/03/2004 (equivalent date containing apostrophe)

So excel appears to treat (in this case when sorting) dates containing apostrophe differently from dates without apostrophes, hence the confusion.

But I suppose the code I have right now should suffice for the moment, as in general it works, but a 100 % full proof macro would be nice.

Do you know if there an equivalent to the ISdate function for checking for valid times?

Thanks,

Luke
 

Watch MrExcel Video

Forum statistics

Threads
1,122,483
Messages
5,596,405
Members
414,064
Latest member
Duncthegreat

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
Top