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" <catabouche@_nospam_web.de> wrote in message
news:g6criv$3q5$(E-Mail Removed)...
> 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" <catabouche@_nospam_web.de> wrote in message
> news:g6criv$3q5$(E-Mail Removed)...
>> 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" <catabouche@_nospam_web.de> wrote in message
news:g6d37p$in5$(E-Mail Removed)...
> 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" <catabouche@_nospam_web.de> wrote in message
>> news:g6criv$3q5$(E-Mail Removed)...
>>> 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" <catabouche@_nospam_web.de> wrote in message
> news:g6d37p$in5$(E-Mail Removed)...
>> 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" <catabouche@_nospam_web.de> wrote in message
>>> news:g6criv$3q5$(E-Mail Removed)...
>>>> 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