2

I am looking for a way to determine if text entered into an Excel cell is following this format: "0000/00/00" (0s can be any one digit number {0,1,2,3,4,5,6,7,8,9}).

Is there a way to check if the cell has a formatting of "0000/00/00"?

Things I already know:

  • That I can check it using something DATEVALUE() function in a data validation rule for the cell, the problem with this method is that it would not accept 1001/04/07 as a valid date.
  • Trying to see if the text contains two "/" characters and that its length is equal to 10 characters. some data validation formula like: "=OR(AND(LEN(A16)=10,(LEN(A16)-LEN(SUBSTITUTE(A16,"/",""))=2),NOT(FALSE)))", the problem with this validation rule is that it would accept entries like "0550//0014" as valid.

P.S: I am working in an environment where using macros or extensions is not allowed!

4
  • 1
    Just be specific about your restrictions, and write a formula that follows them. You need to know if the first four characters are a number, the fifth is a "/", the next two are a number, the eighth is another "/", the next two are a number, and there aren't any more? =IF(AND(ISNUMBER(VALUE(MID(A1,1,4))),ISNUMBER(VALUE(MID(A1,6,2))),ISNUMBER(VALUE(MID(A1,9,2))),MID(A1,5,1)="/",MID(A1,8,1)="/",LEN(A1)=10),TRUE) does exactly that (no more and no less). You know how to do this... – Alex M Nov 6 '19 at 17:40
  • @AlexM How can I choose your comment as the answer? Cause it was so informative and exactly what I needed! – JackBixuis Nov 6 '19 at 18:42
  • Awesome, glad I could help. I was just too lazy to do the formatting as an answer so I made a comment instead. If you type it up as an answer (include screenshots of how well it works for you with sample data), you can select it as the correct answer after a certain waiting period. @me when you do and I'll upvote your answer! – Alex M Nov 6 '19 at 19:29
  • @AlexM I just did thankyou – JackBixuis Nov 6 '19 at 21:28
2

The following user defined function will return TRUE if the argument has the proper format otherwise False:

Option Explicit

Public Function FormatCheck(r As Range) As Boolean
    Dim s As String, s2 As String, arr, i As Long

    FormatCheck = False
    s = r(1).Text
    s2 = Replace(s, "/", "")

    If Len(s2) <> 8 Then Exit Function
    arr = Split(s, "/")

    If UBound(arr) <> 2 Then Exit Function
    If Len(arr(0)) <> 4 Then Exit Function
    If Len(arr(1)) <> 2 Then Exit Function
    If Len(arr(2)) <> 2 Then Exit Function

    For i = 1 To 8
        If Not Mid(s2, i, 1) Like "[0-9]" Then Exit Function
    Next i
    FormatCheck = True
End Function

enter image description here

4
  • Thank you so very much for this wonderfully amazing workaround but what if I can not really use macros? The settings of the environment are such that they do not accept or allow use of vba scripting or macros. – JackBixuis Nov 6 '19 at 14:41
  • Also you sure this function would work properly? Cause it would never get passed the line: FormatCheck = False right? – JackBixuis Nov 6 '19 at 14:44
  • 1
    @JackBixuis You would then create a rather long formula that replicates the same set of logical tests. – Gary's Student Nov 6 '19 at 14:44
  • 1
    @JackBixuis The reason the function works is that the default value is False. If any of the sub-tests fail, that default value gets returned. If all the sub-tests pass, then True gets returned. – Gary's Student Nov 6 '19 at 14:48
2

It's not the pretty formula in the world, but this might work for you. =IFERROR(IF(CONCATENATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))*1>1,"TRUE"),"FALSE")

enter image description here

Hope this helps

1
  • doesn't check for "/"s – Alex M Nov 6 '19 at 17:41
1

Considering that @AlexM has given the answer as a comment and that asked me to answer my question using his strategy, here is what did exactly what I needed.

I set the Data validation rule to:

=IF(AND(ISNUMBER(VALUE(MID(A1,1,4))),ISNUMBER(VALUE(MID(A1,6,2))),ISNUMBER(VALUE(MID(A1,9,2))),MID(A1,5,1)="/",MID(A1,8,1)="/",LEN(A1)=10),TRUE)

And here is what I got:

enter image description here

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.