"Angela" wrote:
Just need to add a validation to above function w.r.t.
below senarios.
I'm having trouble dealing with different types/formats
of dates as well as need not to calculate incase there
is no date.

Assuming you want to return the null string for all invalid conditions:


Function AgeBucket(origDate) As String
Const maxAge As Long = 365
Dim ageLimit As Variant
Dim ageRng As Variant
Dim x As Long
AgeBucket = ""
If IsEmpty(origDate) Then Exit Function
If Not WorksheetFunction.IsNumber(origDate) _
Then Exit Function
x = Date - origDate
If x < 0 Or x maxAge Then Exit Function
ageLimit = Array(0, 8, 15, 22, 31, 46, 61)
ageRng = Array("0-7", "8-14", "15-21", "22-30", _
"31-45", "46-60", "60")
AgeBucket = WorksheetFunction.Lookup(x, ageLimit, ageRng)
End Function


Angela wrote:
4 Numeric 123 #VALUE!

You cannot distinguish between 123 and a valid date. Excel dates are simply
integers, namely the number of days since 12/31/1899, which Excel display as
1/0/1900. The number 123 is the date 5/2/1900.

In order to cover this case, I added the constant maxAge. It is currently
to 365 (one year). Make that larger or smaller as you wish to weed out
out-of-range numbers that are probably not dates.

Angela wrote:
I have added a ' comma in the start to make it look
like a text date--- '40732)

The character is an apostrophe (aka single-quote), not a comma. It is
unclear whether you want to allow that as long as it represents a valid date
(i.e. less than TODAY(), but not by more than maxAge days), or if you want
to disallow it as text, which it is.

The implemenation above disallows it as text. If you want to allow it,
change WorksheetFunction.IsNumber to IsNumeric, a VBA function.

-----

Alternatively, you could use Excel Data Validation to disallow any data
entry other than a date. You can even specify the range of acceptable
dates.

In that case, the function can be simplified as follows:


Function AgeBucket(origDate) As String
Dim ageLimit As Variant
Dim ageRng As Variant
If IsEmpty(origDate) Then AgeBucket = "": Exit Function
ageLimit = Array(0, 8, 15, 22, 31, 46, 61)
ageRng = Array("0-7", "8-14", "15-21", "22-30", _
"31-45", "46-60", "60")
AgeBucket = WorksheetFunction.Lookup(Date - origDate, _
ageLimit, ageRng)
End Function