Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking Military Dates
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking Military Dates
On Sat, 12 Jul 2003 10:05:42 -0400, "Otto Moehrbach"
wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking Military Dates
Ron
Thanks for your help. I think that will suit me to a Tee. Otto "Ron Rosenfeld" wrote in message ... On Sat, 12 Jul 2003 10:05:42 -0400, "Otto Moehrbach" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking Military Dates
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 -- John johnf202 at hotmail dot com "Otto Moehrbach" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking Military Dates
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 Otto Moehrbach wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking Military Dates
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 "Otto Moehrbach" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking Military Dates
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. In article , "Otto Moehrbach" wrote: Ron Thanks for your help. I think that will suit me to a Tee. Otto "Ron Rosenfeld" wrote in message ... On Sat, 12 Jul 2003 10:05:42 -0400, "Otto Moehrbach" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking Military Dates
On Sat, 12 Jul 2003 09:48:21 -0600, "J.E. McGimpsey"
wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking Military Dates
In article ,
Ron Rosenfeld wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking Military Dates
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking Military Dates
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 "J.E. McGimpsey" wrote in message ... 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. In article , "Otto Moehrbach" wrote: Ron Thanks for your help. I think that will suit me to a Tee. Otto "Ron Rosenfeld" wrote in message ... On Sat, 12 Jul 2003 10:05:42 -0400, "Otto Moehrbach" wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking Military Dates
Amen. Otto
"J.E. McGimpsey" wrote in message ... In article , Ron Rosenfeld wrote: 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking Military Dates
Hi Otto!
"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) Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Otto Moehrbach" wrote in message ... 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking Military Dates
On Sat, 12 Jul 2003 16:32:35 -0400, "Otto Moehrbach"
wrote: 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking Military Dates
You already told Otto this on 4 July:
---------- Hi Otto! You have two good solutions, but here's a comment: "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. -- Regards Norman Harker MVP (Excel) ------------------------ -- Regards, Tom Ogilvy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking Dates against Bank Holidays | Excel Worksheet Functions | |||
Spell Checking with checking cell notes | Excel Discussion (Misc queries) | |||
Importing dates and military time from Excel into word doc mail me | Excel Discussion (Misc queries) | |||
Sumproduct checking between two dates | Excel Worksheet Functions | |||
Military time comes up? | Excel Discussion (Misc queries) |