Add 1 Year to DateTime for formula

[Hueby]

Hi all, using CR10:

I'm running a formula on a DateTime formatted table.

We have Drug Dates entered on the day they took a test. I'm trying to write a report that will let us know when a drug test is expiring within 30 days or if it has already expired. Drug Test's are good for 1 year exactly from the Drug Date it was performed on.

My thought was to add 365 days to the Drug_Date to create the expiration date and then have it check if it's within 30 days of the CurrentDate or already well past the CurrentDate. Am I on the right track.. help!

Below is what I have:

if {HR_EMP_DRUG_DET_MC.Drug_Test_Code} = 'DRUG ' and ({HR_EMP_DRUG_DET_MC.Employee_Drug_Date}+365 < currentdate+29) OR ({HR_EMP_DRUG_DET_MC.Employee_Drug_Date} < currentdate) then
crBold else crRegular;

 

[IanWaterman]

What is your question? Does your formula do what you want?

To add precisely a year which covers leap years you can also use

dateadd("yyyy", 1, {HR_EMP_DRUG_DET_MC.Employee_Drug_Date})

Ian

 

[Hueby]

Okay, well it wasn't before... now I'm using this and it seams to be working.

if {HR_EMP_DRUG_DET_MC.Drug_Test_Code} = 'DRUG ' and (dateadd("yyyy", 1, {HR_EMP_DRUG_DET_MC.Employee_Drug_Date}) < currentdate) or (dateadd("yyyy", 1, {HR_EMP_DRUG_DET_MC.Employee_Drug_Date}) in currentdate+29 to currentdate) then
crBold else crRegular;

I don't have data to test the 'within 30 days of expiring part' but I'm using this code hoping it is correct:

(dateadd("yyyy", 1, {HR_EMP_DRUG_DET_MC.Employee_Drug_Date}) in currentdate+29 to currentdate)

Thanks.

 

[IanWaterman]

To test it

hard code a dummy date

Replace your field with

Date(2010,05,01)

Any see if colours change

Ian

 

[lbass]

I think you should reverse the order to:

dateadd("yyyy", 1, {HR_EMP_DRUG_DET_MC.Employee_Drug_Date}) in currentdate to currentdate + 29

-LB