Macro to highlight cells not containing valid dates

[Lucas in London]

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

 

[Mudface]

Here's a simple routine- just highlight the cells you want to check and run the macro-

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]

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]

Hello,

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

Thanks,

Lucas (In London, England, Great Britain)

 

[TryingToLearn]

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]

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