Checking that cell value is date time (ISO)

1

votes

Want to check if the cell is a date time value using the ISO format i.e. 2012-04-12T00:00:00

Current try:

If mainsht.Cells(r, 6).Value = "" Or mainsht.Cells(r, 6).Value = "####-##-##T##:##:## Then
GoTo next6

Still it does not seem to match the format in vba and cell value, as I have many cells with this correct format and still activating the else statement i.e. not recognized by the "####-##-##T##:##:##".

Maybe yyyy-mm-ddThh-MM-ss?

vbaexcel

When using wild cards you should use Like, not = - Tim Williams

Are you trying to tell whether there is a string in the cell which looks like a date, or are you trying to tell whether there is a number in the cell which has been formatted in that particular format? E.g. the current date/time (for me) is the number 42569.628827662 which can be formatted in many different ways, but is still a date. (And is still a number too.) - YowE3K

@TimWilliams Thanks, this is the answer I am looking for. - J.H

@YowE3K ISO Formatted date - J.H

2

votes

ISO date come in several formats, adding an asterisk "####-##-##T##:##:##*" would be more versatile.

2011-01-01T12:00:00Z
2011-01-01T12:00:00+05:00
2011-01-01T12:00:00-05:00
2011-01-01T12:00:00.05381+05:00

Example:

If mainsht.Cells(r, 6).Value = "" Or mainsht.Cells(r, 6).Value Like "####-##-##T##:##:##*" Then

You might want to look at this post: Parsing an ISO8601 date/time (including TimeZone) in Excel

0

votes

The following UDF¹ can be used as a worksheet function or a helper function in a VBA project.

Option Explicit

Function IsISODateTime(str As String)
    Dim n As Long, nums() As Variant
    Static rgx As Object, cmat As Object

    'with rgx as static, it only has to be created once; beneficial with repeated calls to the UDF
    If rgx Is Nothing Then
        Set rgx = CreateObject("VBScript.RegExp")
    End If
    IsISODateTime = vbNullString

    With rgx
        .Global = False
        .MultiLine = False
        .Pattern = "[0-9]{4}\-[0-9]{2}\-[0-9]{2}[A-Z]{1}[0-9]{2}\:[0-9]{2}\:[0-9]{2}"
        IsISODateTime = .Test(str)
    End With
End Function

The UDF returns a true boolean True/False.

The pattern I've provided is very brick-by-brick literal; it could be shortened using the methods detailed in How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops.

¹ _{A User Defined Function (aka UDF) is placed into a standard module code sheet. Tap Alt+F11 and when the VBE opens, immediately use the pull-down menus to Insert ► Module (Alt+I,M). Paste the function code into the new module code sheet titled something like Book1 - Module1 (Code). Tap Alt+Q to return to your worksheet(s).}

0

votes

As already pointed out by Tim Williams do it like so

If mainsht.Cells(r, 6).Value = "" Or mainsht.Cells(r, 6).Value Like "####-##-##T##:##:##" ...

0

votes

To test the cell for an ISO formatted date you should check the cells NumberFormat property, so your If statement should be:

If mainsht.Cells(r, 6).Value = "" Or mainsht.Cells(r, 6).NumberFormat Like "yyyy-mm-ddThh:mm:ss*" Then

Note: If the currently accepted solution is working, your cell only contains a string value (which looks like an ISO formatted date) rather than an actual date displayed using an ISO date format.

Checking that cell value is date time (ISO)

4 Answers