Need Formula to Select Dates 1

[byrne1]

I need a formula that will allow me to select customer who have birthdays coming in the next XX days. This would normally be easy to do but I have two issues:

1) The date field in my DB is stored in a numeric field as MMDDYY (where January 24, 1983 would be stored as 12483);

2) How do I account for year end (ie, if I run the report in December for birthdays in the next 60 days, how will it know to pull January and February birthdays?)

I have been struggling with this for some time now and I really hope someone can offer a solution. Thank you in advance for your help!

 

[lbass]

You could try something like this:

if len(totext({table.field},0,"")) = 5 then
date(1900+val(right(totext({table.field},0,""),2)),val(left(totext({table.field},0,""),1)), val(mid(totext({table.field},0,""),2,2)))else
if len(totext({table.field},0,"")) = 6 then
date(1900+val(right(totext({table.field},0,""),2)),val(left(totext({table.field},0,""),2)), val(mid(totext({table.field},0,""),3,2)))

This will work for those born in the 1900's. This converts the field to a date field which you can then use in your calculations.

-LB

 

[byrne1]

OK, the formula works great in giving me a date from the field value. Now how would I retrieve records that have birthdays within the next 30 days?

 

[lbass]

For birthdays in the next 60 days, try the following. If you want the next 30 days, change the 60s to 30:

date(year(currentdate),month({@date}),day({@date})) in date(year(currentdate), month(currentdate), day(currentdate)) to dateserial(year(currentdate), month(currentdate), day(currentdate)+60) or
date(year(currentdate)+1,month({@date}),day({@date}))in date(year(currentdate), month(currentdate), day(currentdate)) to dateserial(year(currentdate), month(currentdate), day(currentdate)+60)

-LB