Checking Military Dates

[Otto Moehrbach]

Excel 2002, Win XP
I'm working with the new military 8 digit date system.
For instance 20030704 is 4 July 2003.
I need to check if the user entry is a valid date. I'm using:
DateFrom8 = DateSerial(Left(j, 4), Mid(j, 5, 2), Right(j, 2)) to convert the
8 digits into the intended date. The conversion works fine.
The problem is when I try to check if the user entered the 8 digits
correctly. IOW, 13 months is bad, 32 days is bad. But the above DateSerial
function will simply take the 13 months as being one month more than the
date if 12 were entered. The same with too many days. No matter what the
numbers entered, DateSerial will produce a valid date. Using
IsDate(DateSerial(...........) will always produce a True.
My question: Is there a function other than DateSerial that I can use
with IsDate to show False if the user enters too many months or days? Or is
there another way to check the validity of the date?
Obviously I can check Mid(j, 5, 2) and see if it's >12. The problem is
more with the days.
Thanks for your help. Otto

 

[Ron Rosenfeld]

Excel 2002, Win XP
I'm working with the new military 8 digit date system.
For instance 20030704 is 4 July 2003.
I need to check if the user entry is a valid date. I'm using:
DateFrom8 = DateSerial(Left(j, 4), Mid(j, 5, 2), Right(j, 2)) to convert the
8 digits into the intended date. The conversion works fine.
The problem is when I try to check if the user entered the 8 digits
correctly. IOW, 13 months is bad, 32 days is bad. But the above DateSerial
function will simply take the 13 months as being one month more than the
date if 12 were entered. The same with too many days. No matter what the
numbers entered, DateSerial will produce a valid date. Using
IsDate(DateSerial(...........) will always produce a True.
My question: Is there a function other than DateSerial that I can use
with IsDate to show False if the user enters too many months or days? Or is
there another way to check the validity of the date?
Obviously I can check Mid(j, 5, 2) and see if it's >12. The problem is
more with the days.
Thanks for your help. Otto

I believe DateValue only accepts valid dates. So you could transform the input
into a string, and then apply DateValue. Something like:

==============
Sub ValiDATE()
On Error GoTo err
MsgBox (DateValue(Mid(Selection, 5, 2) & "/" & Right(Selection, 2) & "/" &
Left(Selection, 4)))
Exit Sub
err: MsgBox ("Invalid Date")
End Sub
=============

--ron

 

[Otto Moehrbach]

Ron
Thanks for your help. I think that will suit me to a Tee. Otto

 

[jaf]

Hi Otto,
B2 doesn't work but B3 does.

Sub CheckDate()
b1 = "20030231"
b2 = DateSerial(Left(b1, 4), Mid(b1, 5, 2), Right(b1, 2))

b3 = Mid(b1, 5, 2) & "/" & Right(b1, 2) & "/" & Left(b1, 4) 'mm/dd/yyyy
Debug.Print b1, b2, b3, IsDate(b3)

End Sub

 

[Tom Ogilvy]

Public Function chkdate(j)
Dim sYear As String, sMon As String
Dim sDay As String
sYear = Left(j, 4)
sMon = Mid(j, 5, 2)
sDay = Right(j, 2)
If CLng(sMon) < 1 Or CLng(sMon) > 12 Then _
chkdate = False: Exit Function
chkdate = IsDate(Format(DateSerial(Year(Date), CLng(sMon), 1), "mmm") _
& " " & sDay & ", " & sYear)
End Function


I think you are stuck with checking the month against 12.

Regards,
Tom Ogilvy

 

[Tom Lorenzo]

Otto,

You can check it in code. Simple example below:

' Assume 8 digit entry is required
If checkLength <> 8 Then
MsgBox "Improper date -- need 8 characters"
' Code for improper length of entry -- might include Exit Sub since
parsing the year,month,day will not work correctly
End If

numDay = Right(j, 2)
numMonth = Mid(j, 5, 2)
numYear = Left(j, 4)

' Create a date as end of month for user entered year,month,day
testDate = DateSerial(numYear, numMonth + 1, 1) - 1
checkDay = Day(testDate)

' Check if entered day is greater than days in assumed month
If numDay > checkDay Then
' code for incorrect day entry
MsgBox numDay & " cannot be greater than " & checkDay
End If

If (numMonth > 13) Or (numMonth < 1) Then
MsgBox numMonth & " is not a valid month"
' code to handle improper month entry
End If

' etc.
dateFrom8 = DateSerial(numYear, numMonth, numDay)

............................................
Easy to extend for additional error checking. For example, there are
probably year values such as "1921" that give pefectly acceptable dates but
that are not appropriate for your spreadsheet.

The tougher problem is deciding what you want to do when you encounter
invalid dates -- do you want to correct it in the spreadsheet, give a prompt
(messagebox), flag it in the spreadsheet that it's invalid and the reason
it's invalid, or something else?

Regards,

Sox

 

[J.E. McGimpsey]

I think DateValue tries too hard to interpret a value as a date. It
will recognize both 20030113 and 20031301 as valid dates. The latter
is not.

 

[Ron Rosenfeld]

I think DateValue tries too hard to interpret a value as a date. It
will recognize both 20030113 and 20031301 as valid dates. The latter
is not.

Thank you for pointing that out. That seems contrary to the HELP information:

"If date is a string that includes only numbers separated by valid date
separators, DateValue recognizes the order for month, day, and year according
to the Short Date format you specified for your system".

Obviously, it is not doing that on my system (or yours).

So one would have to test each component individually.

Something like:

===========================
Option Explicit

Sub ValiDATE()
Dim Yr As Integer
Dim Mnth As Integer
Dim Dy As Integer

Yr = Int(Selection / 10 ^ 4)
Mnth = Int(Selection / 100) Mod 100
Dy = Selection Mod 100

If Yr < 1900 Or Yr > 2100 Then GoTo err
If Mnth < 1 Or Mnth > 12 Then GoTo err
If Month(DateSerial(Yr, Mnth, Dy)) <> Mnth Then GoTo err

MsgBox (DateSerial(Yr, Mnth, Dy))
Exit Sub

err: MsgBox ("Invalid Date")
End Sub
=======================

Or he could use the same algorithm in a Data Validation routine.


--ron

 

[J.E. McGimpsey]

That seems contrary to the HELP information

Perhaps it's my using MacOffice HELP, which, though getting better,
still is not really definitive, but my philosophy is that HELP is to
be consulted and used, not believed...

Trust, but verify.

<vbg>

 

[Harald Staff]

If Format(dtTest, "yyyymmdd") = j Then

Now this is a great idea if I ever saw one, Heiko. But it is imo too sensitive to regional
settings and to nonsense input. Alow me to combine our ideas into yet another suggestion:

Public Function isValidDate(L) As Boolean
Dim D As Date
On Error Resume Next
D = DateSerial(L \ 10000, L \ 100 Mod 100, L Mod 100)
isValidDate = (CStr(L) = Format(D, "yyyymmdd"))
End Function

Sub test()
MsgBox isValidDate(20020131)
MsgBox isValidDate(20020231)
MsgBox isValidDate(4)
MsgBox isValidDate("Beer")
End Sub

Best wishes Harald
Excel MVP

Followup to newsgroup only please.

 

[Otto Moehrbach]

J.E.
I caught that. I would use a check of Mid(j, 5, 2)<13 in conjunction
with the DateValue that Ron suggested. Thanks. Otto

 

[Otto Moehrbach]

Amen. Otto

Perhaps it's my using MacOffice HELP, which, though getting better,
still is not really definitive, but my philosophy is that HELP is to
be consulted and used, not believed...

Trust, but verify.

<vbg>

 

[Norman Harker]

Hi Otto!

Re:
"new military 8 digit date system"

Just an aside.

This is going to become more and more common

This is in compliance with ISO 8601:2000 which confirmed the previous
standards that have been around since at least 1988. Quite a few
computer users noted even earlier that date entry in this form made
sorting on earliest / latest easier.

That standard for dates uses yyyymmdd or an approved separated form of
yyyy-mm-dd

Its clear that the military have decided that if two or three get
together to attack on 03/09/04, we better make sure we do it on the
same day! At the moment the Chinese would go in on 4-Sep-2003, The US
would go in on 9-Mar-2004, and the Australians would go in on
3-Sep-2004.

I think that the system is becoming more common in European Economic
Community countries as well but I haven't seen it "Downunder" yet.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Sunday: Myanmar (Full Moon of Waso); Sri
Lanka (Poson Full Moon Poya Day); Thailand (Asalha Puja); Yugoslavia
(Freedom Rising Day Montenegro). Ashala Puja (Buddhism); O-Bon /
Festival of Souls (Shinto)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Ron Rosenfeld]

I caught that. I would use a check of Mid(j, 5, 2)<13 in conjunction
with the DateValue that Ron suggested. Thanks. Otto

I now think the macro I posted after JE pointed out the problem is a better
way. In addition to checking that the Month is 1-12, it also checks to make
sure the day is 1 to Maximum number of days in the particular Month.


--ron

 

[Tom Ogilvy]

You already told Otto this on 4 July:

----------
Hi Otto!

You have two good solutions, but here's a comment:

Re:
"He is in a military environment and apparently the military, at least
at his base, has converted to writing all dates in an 8 digit format.
4
July 03 would be 20030704"

Get used to this. It's the ISO8601:2000 standard form of non-separated
date representation. Most countries subscribe to the ISO but getting
them to adopt this standard for of date representation is going to be
very difficult.

Many computer buffs have used it for years as it enables easy sorting
of dates into earliest > latest. It also happens to be the form used
by the Chinese.

Perhaps in some future version of Excel we might see a "pre-formatted
as date" cell actually interpret this form as a date.