Checking date format in Excel using a macro

[Matt27]

I have a spreadsheet for users to use and one of the cells requires users to input a date in format dd/mm/yyyy. I want to be able to check that a valid date has been input before the file is saved or closed. I have gone into Visual Basic and used the BeforeClose and BeforeSave. I have tried putting the IsDate function in both but this allows dates such as 66/66/06 and 33/33/03!! Has anyone got any suggestions?

 

[KenWright]

Can you bound the possible dates that would be acceptable and then use that as part of your checking?

You could also interrogate each part of the date from another cell and confirm that it falls within acceptable bounds, eg less than or equal to 31 for a day, less than or equal to 12 for a month and so on:-

=(--LEFT(TEXT(A1,"dd/mm/yyyy"),2)<=31)+(--MID(TEXT(A1,"dd/mm/yyyy"),4,2)<=12)+(AND((--RIGHT(TEXT(A1,"dd/mm/yyyy"),4)>=2006),(--RIGHT(TEXT(A1,"dd/mm/yyyy"),4)<=2010)))

This interrogates all 3 sections and then gives you a 3 if all is well. Just have the BeforeClose/BeforeSave check that cell for a 3 and it should work I think.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[combo]

You can use data validation, with 'whole number' option set, and max and min values. If you like to to limit data ranges, write boundary dates in separate cells and format 'general'. Data validation will allow to display entry and error messages too.

combo

 

[cheerio]

The following function will test whether the data is a valid date in dd/mm/yyyy format. The basic logic converts the input text to a date and then converts that date back to a dd/mm/yyyy string. If the resulting string matches the input string you have a meaningful date in the required format.

You could use it with data validation to test at the time data is entered.

Public Function RealDate(strTest) As Boolean
Dim dteTest  As Date

If IsDate(strTest) Then
'   more testing required
    On Error Resume Next
    dteTest = DateSerial(Int(Right(strTest, 4)), Int(Mid(strTest, 4, 2)), Int(Left(strTest, 2)))
    ' an error arises if string looks like a date but is not in required format of dd/mm/yyyy
    If Err.Number <> 0 Then
        RealDate = False
        Err.Clear
    ElseIf strTest = Format(dteTest, "dd/mm/yyyy") Then
       RealDate = True
    Else
       RealDate = False
    End If
Else
'  cannot be a date
   RealDate = False
End If

 

[macropod]

Matt27,

I'm with combo on this one: use Data|Validation, from the Excel menu - no need for any macros.

If you set the required format to 'Date', you can do things like restricting even real dates to a pre-defined date range, or you can accept any valid date (eg by setting the property as 'not equal to' and using "29/1/1900" as the excluded date - this has to be about the only good use there is for that date, which Excel otherwise wrongly recognises as valid).

Cheers

[MS MVP - Word]

 

[SkipVought]

Matt,

A FORMAT is just a DISPLAY FORMAT.

You need to be concerned with the underlying VALUE. Get a better understanding of Date/Time.

Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[firefytr]

Because a date in Excel is a Serial Number, there is no real way to detect if a number is a date or if it is just a number. There is a VBA function that let's you do this, the IsDate() method. So there is no way to tell the difference between a number that *could* be a date and a date *formatted* as a date.

Here is a way to use it in the worksheet ...

http://vbaexpress.com/kb/getarticle.php?kb_id=323

HTH

-----------
Regards,
Zack Barresse