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