Angela said:

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

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.

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