Is there anyway to check with VBA if a date is entered as DD-MM-YY or MM-DD-YY?

How can I tell using VBA if 4-5-18 is actually 4th of May of Fifth of April?

Jan

On 5/4/2018 2:31 AM, wrote:
Is there anyway to check with VBA if a date is entered as DD-MM-YY or MM-DD-YY?

How can I tell using VBA if 4-5-18 is actually 4th of May of Fifth of April?

If that's all you have in isolation you can't...if there are a string of
dates such that can find a value 12 in the (presumed) month field then
you can make the presumption that's days and the other must be months
but without some additional hints or such a specific day that can be
recognized there's just insufficient data to be unequivocal.

--

dpb wrote:

On 5/4/2018 2:31 AM, wrote:
Is there anyway to check with VBA if a date is entered as DD-MM-YY or
MM-DD-YY?

How can I tell using VBA if 4-5-18 is actually 4th of May of Fifth of
April?

If that's all you have in isolation you can't...if there are a string of
dates such that can find a value 12 in the (presumed) month field then
you can make the presumption that's days and the other must be months
but without some additional hints or such a specific day that can be
recognized there's just insufficient data to be unequivocal.

On the other hand, if the cell is properly formatted as a date, you can check
the NumberFormat property:

Dim tmp As Variant
tmp = Split(ActiveCell.NumberFormat, "/")
If UBound(tmp) 0 Then
Select Case LCase(tmp(0))
Case "d", "dd", "ddd", "dddd"
'd/m/y
Case "m", "mm", "mmm", "mmmm", "mmmmm"
'm/d/y
Case Else
'not formatted as date
End Select
End If

--
- Were you looking for something?
- A sense of the miraculous in everyday life.

On 5/4/2018 10:55 AM, Auric__ wrote:
dpb wrote:

On 5/4/2018 2:31 AM, wrote:
Is there anyway to check with VBA if a date is entered as DD-MM-YY or
MM-DD-YY?

How can I tell using VBA if 4-5-18 is actually 4th of May of Fifth of
April?

If that's all you have in isolation you can't...if there are a string of
dates such that can find a value 12 in the (presumed) month field then
you can make the presumption that's days and the other must be months
but without some additional hints or such a specific day that can be
recognized there's just insufficient data to be unequivocal.

On the other hand, if the cell is properly formatted as a date, you can check
the NumberFormat property:

Dim tmp As Variant
tmp = Split(ActiveCell.NumberFormat, "/")
If UBound(tmp) 0 Then
Select Case LCase(tmp(0))
Case "d", "dd", "ddd", "dddd"
'd/m/y
Case "m", "mm", "mmm", "mmmm", "mmmmm"
'm/d/y
Case Else
'not formatted as date
End Select
End If


But if the cell is formatted as Date and contains the data, then it will
already be interpreted as whichever and all need to do is =MONTH() or
=DAY() and inspect return value to know...

Didn't seem as that was the OP's question/problem at least way it came
across to my reading...guess we'll say if comes back to amplify.

--

On 5/4/2018 10:55 AM, Auric__ wrote:
dpb wrote:

On 5/4/2018 2:31 AM, wrote:
Is there anyway to check with VBA if a date is entered as DD-MM-YY or
MM-DD-YY?

How can I tell using VBA if 4-5-18 is actually 4th of May of Fifth of
April?

If that's all you have in isolation you can't...if there are a string of
dates such that can find a value 12 in the (presumed) month field then
you can make the presumption that's days and the other must be months
but without some additional hints or such a specific day that can be
recognized there's just insufficient data to be unequivocal.

On the other hand, if the cell is properly formatted as a date, you can
check
the NumberFormat property:

Dim tmp As Variant
tmp = Split(ActiveCell.NumberFormat, "/")
If UBound(tmp) 0 Then
Select Case LCase(tmp(0))
Case "d", "dd", "ddd", "dddd"
'd/m/y
Case "m", "mm", "mmm", "mmmm", "mmmmm"
'm/d/y
Case Else
'not formatted as date
End Select
End If


But if the cell is formatted as Date and contains the data, then it will
already be interpreted as whichever and all need to do is =MONTH() or =DAY()
and inspect return value to know...

Didn't seem as that was the OP's question/problem at least way it came across
to my reading...guess we'll say if comes back to amplify.

Hmm.., I rather like Auric's simplified solution since it indeed does EXACTLY
what I interpret the OP is looking to accomplish.

Note also that Excel uses the 'system' date format unless set otherwise for
specific cells. For example, after XP the format order for d/m got switched.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On 5/4/2018 4:37 PM, GS wrote:
....

Hmm.., I rather like Auric's simplified solution since it indeed does
EXACTLY what I interpret the OP is looking to accomplish.
....

Well, it'll tell him what the cell is formatted as; whether that's what
the data was when entered isn't determinable from the string which is
where _I_ thought OP was coming from... :)

Once it's in the cell it can be either depending on the format; is that
correct or not is still indeterminate.

--

I'm sorry it has taken me so long to get back, but I have been away from my computer ever since.

I will try some of you suggestions and see if any of them solves my challenge.

Thank you very much for your efforts.

Jan

And an explanation for my problem:

The original issue was and is, that I programmatically have to change alle dates to dd-mm-yyyy format. The dates are imported from other systems, and some of these are in the format mm-dd-yyyy. Dates that are already dd-mm-yyyy shall not be converted, so I want to check the format before conversion.

When the sheet is done, the data will exported to another system, who cannot check if the dates are in the right format, but expect them to be dd-mm-yyyy. Therefore I (or rather those who uses the sheet) have to maked sure all dates are formated correctly be the export.

Jan

On 5/7/2018 3:52 AM, wrote:
And an explanation for my problem:

The original issue was and is, that I programmatically have to change alle dates to dd-mm-yyyy format. The dates are imported from other systems, and some of these are in the format mm-dd-yyyy. Dates that are already dd-mm-yyyy shall not be converted, so I want to check the format before conversion.

When the sheet is done, the data will exported to another system, who cannot check if the dates are in the right format, but expect them to be dd-mm-yyyy. Therefore I (or rather those who uses the sheet) have to maked sure all dates are formated correctly be the export.

If the data are correctly formatted based on the input from whence they
came, then there's no reason not to just convert all to dd-mm-yyyy; it's
a "do-nothing" for those that already are, so there's no penalty.

Auric's function will let you do it selectively, but there's probably
not enough of a time penalty that it makes any difference whether use it
or not.

Where you've still got a problem is if the data were imported but not
internally coded to match the external format; then you've still got the
issue that the values of ambiguous dates can't be distinguished solely
by their string value altho as GS points out, inside Excel they will
have been read according to the system default format in which case
switching the formatting changes the appearance in the cell but not the
value which raises the question of whether the dates in the sheet are
actually correct or not depending upon the input source and the system
setting and in what form the values were on input--was it from a date
string or a serial number?

--

"as GS points out, inside Excel they will have been read according to the
system default format"

See my latest reply to yours regarding "DateText" vs date interpretation of
text being imported with date values.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

El viernes, 4 de mayo de 2018, 2:31:09 (UTC-5), escribió:
Is there anyway to check with VBA if a date is entered as DD-MM-YY or MM-DD-YY?

How can I tell using VBA if 4-5-18 is actually 4th of May of Fifth of April?

Jan


x="4-5-18"
?format(x,"mm/dd/yyyy") 'Return 05/04/2018
?format(x,"Long date") 'Return viernes, 4 de mayo de 2018