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!
3 Answers
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
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")
Hope this helps
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:
