Question Re: Using DATEADD and QTR functions in Technology Q&A

Using DATEADD and QTR functions

Former Member — Tue, 19 Apr 2016 16:01:58 GMT

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

Re: Using DATEADD and QTR functions

DellSC — Tue, 19 Apr 2016 16:16:36 GMT

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

Re: Using DATEADD and QTR functions

Former Member — Tue, 19 Apr 2016 17:15:05 GMT

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.

Re: Using DATEADD and QTR functions

DellSC — Tue, 19 Apr 2016 18:40:05 GMT

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

Re: Using DATEADD and QTR functions

Former Member — Tue, 19 Apr 2016 20:18:35 GMT

{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.

Re: Using DATEADD and QTR functions

DellSC — Tue, 19 Apr 2016 21:19:56 GMT

That should actually be:

DateAdd('m', 1, day91);

Sorry! My mistake in my sample.

You could also try:

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

-Dell

Re: Using DATEADD and QTR functions

Former Member — Wed, 20 Apr 2016 13:43:58 GMT

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