Calculating dates

[sivega]

I am writing an if statement and I am having trouble with my expression.

The expression part of the statement is:
IIf(BirthDate<Now()............

Writing the expression this way will cause everything to be true. What I really want is if the month and day portion is < now(). I tried using cdate and datepart, but I just can't get it to work. Any ideas?

Thanks in advance!!!

[rpeare]

iif(cint(datepart("m", birthdate)) < cint(datepart("m", date())) AND cint(datepart("d", birthdate)) < cint(datepart("d", date())), TruePart, FalsePart)

[sivega]

Thank you!!

But I get the data mismatch error when I add the expression to the rest of the statement. Here's the statement in it's entirety:

iif(cint(datepart("m", birthdate)) < cint(datepart("m", date())) AND cint(datepart("d", birthdate)) < cint(datepart("d", date())), CDate(Month([birthdate]) & "/" & Day(birthdate)& "/" &
Year(Now())),CDate(Month(birthdate)& "/" & Day(birthdate) & "/" & DateAdd
("yyyy",-1,Year(Now()))))

Not sure what data is being mismatched....

[RayMilhon]

RPeare I don't think this is what they want. If the birthdate is May 27 then cint(datepart("m",birthdate))) = 5 cint(datepart("m",date())) = 6 so part a = True
cint(datepart("d",birthdate)) = 27 cint(datepart("d",date())) = 14 So part B = false so the IIF = false However May 27 is < June 14.

We need to have a better understanding of what he's trying to do in order to help him. His explanation is a little too brief

[sivega]

Hi!

What I'm trying to do is calculate reporting periods. Reporting periods are 2 years long. The kicker is that some reporting periods run on even years (2012-2014) and others on odd years (2013-2015). The reporting period for each person begins on their DOB. For example Sally's Reporting period is odd so it would begin 4/13/13 and end 4/13/15....

Does this make sense?

I'm a girl by the way

[RayMilhon]

Ok think I understand what you're trying to do. If the birthday is prior to today you want this years birthday date. If the birthday is later than today you want last years birthday date. Here's my suggestion

IIF(cdate(Month(birthdate) & "/" & day(birthdate) & "/" & Year(date()) < date()) ,cdate(Month(birthdate) & "/" & day(birthdate) & "/" & Year(date()) ,cdate(Month(birthdate) & "/" & day(birthdate) & "/" & Year(DateAdd("y",-1,date()) )

[sivega]

Ok think I understand what you're trying to do. If the birthday is prior to today you want this years birthday date. If the birthday is later than today you want last years birthday date. Here's my suggestion

IIF(cdate(Month(birthdate) & "/" & day(birthdate) & "/" & Year(date()) < date()) ,cdate(Month(birthdate) & "/" & day(birthdatse) & "/" & Year(date()) ,cdate(Month(birthdate) & "/" & day(birthdate) & "/" & Year(DateAdd("y",-1,date()) )

Thank you! That worked perfectly.......not what I needed, unfortunately. I was working on this bit with someone but we really didn't think it through. I worked it out and this is what I need:

IIf(Year([Birthdate]) Mod 2=0,CDate(Month([birthdate]) & "/" & Day([birthdate]) & "/" & Year(Date())),CDate(Month([birthdate]) & "/" & Day(DateAdd("d",-1,[birthdate])) & "/" & Year(DateAdd("yyyy",-1,Date()))))

Thanks you both for your help

[rpeare]

My formula will work for the original post, both month and day have to be less than the current date values or it fails. The truepart and false part of your statement could be plugged into mine and it should function identically.