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.

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


"J. Blauth" wrote in message
...
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.

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.

Rick Rothstein (MVP - VB) schrieb:
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


"J. Blauth" wrote in message
...
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.

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" wrote in message
...
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.

Rick Rothstein (MVP - VB) schrieb:
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


"J. Blauth" wrote in message
...
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.

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.

Rick Rothstein (MVP - VB) schrieb:
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" wrote in message
...
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.

Rick Rothstein (MVP - VB) schrieb:
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


"J. Blauth" wrote in message
...
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.

' ===== the next line depends on the local language settings,
' ===== so there might be some work to be done

DateIn = Replace(DateIn, "mmmm", "January")

See if using this line instead makes it work more universally....

DateIn = Replace(DateIn, "mmmm", MonthName(1))

And in the next ElseIf block, replace this...

DateIn = Replace(DateIn, "mmm", "Jan")

with this...

DateIn = Replace(DateIn, "mmm", MonthName(1, True))

Rick