Using DATEADD and QTR functions

[Former Member]

Hello,

I have a report I'm trying to write that needs a specific logic. This is regarding when an employee is eligible for 401K.

The logic is : Eligible the 1st of the month after your 91st day of employment.

The other logic is that the employee enters Cash Profit Sharing the first of the month of the next quarter on their 91st day.

{CRT_EMP_START_DATE.EMPLOYMENT_DATE}

I'm having a bit of an issue understanding how to use the functions crystal reports provides to achieve what the date would be. I know how to use formulas and implement.

Any help is always greatly appreciated. 

Ajax

[DellSC · Accepted solution]

I might do something like this to get the dates:  (I like to break it down into parts...)

401K:

DateVar day91 := DateAdd('d', 91, {CRT_EMP_START_DATE.EMPLOYMENT_DATE});

if Day(day91) > 1 then day91 := DateAdd('m', 91, day91);

Date(Year(day91), Month(day91), 1)

Profit Sharing:

DateVar qtrDate := DateAdd(q, 1, DateAdd('d', 91, {CRT_EMP_START_DATE.EMPLOYMENT_DATE}));

NumberVar qtr := DatePart('q', qtrDate);

Choose(qtr, Date(year(qtrDate), 1, 1), Date(year(qtrDate), 4,1), Date(year(qtrDate), 7, 1), Date(year(qtrDate), 10, 1))

This assumes that you're on calendar quarters that start on Jan 1.

-Dell

[Former Member · Accepted solution]

Thanks for the help. I like to break it into parts as well. I somewhat know what you're doing and am coming upon a couple of errors. The first one is in the 401K where it says:

And this one for Profit Sharing:

I understand what the errors are telling me I'm just not sure why.

Thanks again.

[DellSC · Accepted solution]

Is {CRT_EMP_START_DATE.EMPLOYMENT_DATE} a date field or a string field?  If it's a string, you might have to use this when you include the field in the formula:

Date({CRT_EMP_START_DATE.EMPLOYMENT_DATE})

Also, instead of using

  DateAdd('d', 91', {CRT_EMP_START_DATE.EMPLOYMENT_DATE})

you could try

  {CRT_EMP_START_DATE.EMPLOYMENT_DATE} + 91 and see whether that help.

You'll need to do this in both formulas.

To debug, comment out all but the first line in each formula by placing // at the beginning of each line.  Check to see that you're getting an actual date value from that first line before adding back in the next lines.

-Dell

[Former Member · Accepted solution]

{CRT_EMP_START_DATE.EMPLOYMENT_DATE} is a DateTime field.

It seems that Date({CRT_EMP_START_DATE.EMPLOYMENT_DATE} works then when I comment out:

//If Day(day91) > 1 then day91 := DateAdd('m', 91, day91)

it gives me no errors. If I don't comment out

if Day(day91) > 1 then day91 := DateAdd('m', 91, day91)

then I get the same error. "A Date is required here" but on line:

DateAdd('m', 91, day91)

Any other ideas for debugging or what this might be?

Thank you.

[DellSC · Accepted solution]

That should actually be:

DateAdd('m', 1, day91);

Sorry!  My mistake in my sample. 

You could also try:

DateAdd('m', 1, Date(day91));

-Dell

[Former Member · Accepted solution]

Thank you for your help. With a few tweaks everything worked perfectly!