Hi Rick,
thanks for your support. The function is not working 100% for me but it
points me to the right direction. what I did for a quick result was the
following:
Private Function IsDateFormat(C As Range) As Boolean
Dim BracketClose As Long
Dim SemiColon As Long
Dim DateIn As String
Dim DateTemp As String
If C.Count > 1 Then Exit Function
DateIn = C.NumberFormat
DateIn = LCase(DateIn)
If InStr(1, DateIn, "mmmm", vbTextCompare) > 0 Then
' =====> the next line depends on the local language settings,
' =====> so there might be some work to be done
DateIn = Replace(DateIn, "mmmm", "January")
ElseIf InStr(1, DateIn, "mmm", vbTextCompare) > 0 Then
DateIn = Replace(DateIn, "mmm", "Jan")
ElseIf InStr(1, DateIn, "mm", vbTextCompare) > 0 Then
DateIn = Replace(DateIn, "mm", "1")
ElseIf InStr(1, DateIn, "m", vbTextCompare) > 0 Then
DateIn = Replace(DateIn, "m", "1")
End If
If InStr(1, DateIn, "yyyy", vbTextCompare) > 0 Then
DateIn = Replace(DateIn, "yyyy", "2000")
ElseIf InStr(1, DateIn, "yy", vbTextCompare) > 0 Then
DateIn = Replace(DateIn, "yy", "00")
End If
If InStr(1, DateIn, "dddd", vbTextCompare) > 0 Then
DateIn = Replace(DateIn, "dddd", "1")
ElseIf InStr(1, DateIn, "ddd", vbTextCompare) > 0 Then
DateIn = Replace(DateIn, "ddd", "1")
ElseIf InStr(1, DateIn, "dd", vbTextCompare) > 0 Then
DateIn = Replace(DateIn, "dd", "1")
ElseIf InStr(1, DateIn, "d", vbTextCompare) > 0 Then
DateIn = Replace(DateIn, "d", "1")
End If
DateIn = Replace(DateIn, "h", "1")
DateIn = Replace(DateIn, "m", "1")
DateIn = Replace(DateIn, "s", "1")
BracketClose = InStr(DateIn, "]")
DateIn = Replace(DateIn, ";", "", 1)
DateIn = Replace(DateIn, "@", "", 1)
If BracketClose > 0 Then
DateIn = Mid(DateIn, BracketClose + 1)
End If
DateTemp = DateIn
IsDateFormat = IsDate(DateTemp)
End Function
I have not tested it much but it seems to work smoothly for the few
cases I run into including some user defined date formats like "yy, mmmm
- dd hh:mm:ss".
Thanks again,
Joe.
I'm not 100% sure of this, but I **think** this function does what you
want...
Function IsDateFormat(C As Range) As Boolean
Dim Bracket As Long
Dim SemiColon As Long
Dim DateIn As String
If C.Count > 1 Then Exit Function
DateIn = C.NumberFormat
If InStr(1, DateIn, "mmm", vbTextCompare) > 0 Then
DateIn = Replace(DateIn, "mmm", "Jan")
ElseIf InStr(1, DateIn, "mmmm", vbTextCompare) > 0 Then
DateIn = Replace(DateIn, "mmmm", "January")
End If
If InStr(1, DateIn, "ddd", vbTextCompare) = 0 Or _
InStr(1, DateIn, "dddd", vbTextCompare) = 0 Then
DateIn = Replace(DateIn, "dd", "1")
End If
DateIn = Replace(DateIn, "m", "1")
DateIn = Replace(DateIn, "d", "1")
DateIn = Replace(DateIn, "y", "1")
DateIn = Replace(DateIn, "h", "1")
DateIn = Replace(DateIn, "s", "1")
Bracket = InStr(DateIn, "]")
SemiColon = InStr(DateIn & ";", ";")
IsDateFormat = IsDate(Right(Left(DateIn, SemiColon - 1), Bracket + 1))
End Function
Rick
J. Blauth said:
Hi Rick,
thanks for a quick reply.
You will need to clarify what you consider a date. Your use of "sth"
sorry. you are perfectly right that I missed to thoroughly define what
I exactly consider to be a valid date format.
a valid date time should be any format of the following:
formats that include
1) day, month, year
2) day, month (this case is only a nice-to-have)
3) hour, minute
4) hour, minute, second
5) day, month, year, hour, minute
6) day, month, year, hour, minute, second
in any possible order and local preference.
So case 1 as an example would contain dd.mm.yy, mm/dd/yyyy (...).
by the way, i used the "sth" as an abbreviation for something
kind regards,
Joe.
You will need to clarify what you consider a date. Your use of "sth"
(although I'm not sure what the "t" stands for) seems to suggest you
will accept date parts as being date formatted, is that correct? For
example, would a format of mmmm be considered a date format? I ask
because the VB IsDate function would return False for it. So, I am
looking for your complete definition of what would constitute a "date
format".
Rick
Hi all,
is there a simple way to check if a Range.NumberFormat returns a
Date format ? To be sure I am not misunderstood: the Cell-Values
might be empty but the cells are formatted to a date format. by
default it might be dd/mm/yy or similar but could also return
"[$-409]d/m/yy h:mm AM/PM;@" or sth.
what I am searching for is a simple way to check if the
range.numberformat fits to any possible dateformat. best case would
be "If Range.NumberFormat = IsDateFormat then..." but I cannot seem
to find something like that. Is there an easy solution for that ?
thanks for any kind of hints.
Joe.
