×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# show annivesary dates for next quarter

## show annivesary dates for next quarter

(OP)
I have a report that the user wants to see the service dates for those who meet their 5, 10, 15, 20, etc service date for the next quarter. So, it's 4th quarter now but they want to see who will have a milestone service anniversary next Jan-March.

I have this in the r/s for the quarter but having trouble with getting just those who will meet the 5, 10, 15 year in that quarter. I am getting everyone in the Jan-March and can't figure out a formula just for just the milestone dates. Any help is appreciated - using Crystal Reports 2016.

if DatePart("q", currentdate) = 1 then
month({HIER.CONTRACT_DTE}) in [4,5,6] else
if DatePart("q", currentdate) = 2 then
month({HIER.CONTRACT_DTE}) in [7,8,9] else
if DatePart("q", currentdate) = 3 then
month({HIER.CONTRACT_DTE}) in [10,11,12] else
if DatePart("q", currentdate) = 4 then
month({HIER.CONTRACT_DTE}) in [1,2,3]

### RE: show annivesary dates for next quarter

in english, the below is what you want...i think.

If hiredate is in apr mar june then Q1
else
if hiredate is in july aug sept then Q2
else
if hiredate in oct nov dec then Q3
else
if hiredate in Jan feb mar then Q4

If the above is correct, then you would want change your how your formula evaluates.

#### CODE -->

if month({HIER.CONTRACT_DTE}) in [4,5,6] then 1 else
if month({HIER.CONTRACT_DTE}) in [7,8,9] then 2 else
if month({HIER.CONTRACT_DTE}) in [10,11,12] then 3 else
if month({HIER.CONTRACT_DTE}) in [1,2,3] then 4 

then you would need another formula to evaluate if the returned value matched the current quarter...the example below could be used to suppress unwanted rows, or with a little modification could be used in the Select expert to limit the rows returned (a better approach generally speaking)
ie: NOT({@QtrFormula} = DatePart('q',currentdate))

### RE: show annivesary dates for next quarter

Try this:

Create the following formulas:

#### CODE --> {@Next_Quarter_End_Date}

If      Month(Today) in [1 to 3]
Then    Date(Year(Today), 6, 30)
Else
If      Month(Today) in [4 to 6]
Then    Date(Year(Today), 9, 30)
Else
If      Month(Today) in [7 to 9]
Then    Date(Year(Today), 12, 31)
Else
If      Month(Today) in [10 to 12]
Then    Date(Year(Today) + 1, 3, 31) 

#### CODE --> {@Age_Next_Querter}

Truncate(DateDiff('d', {HIER.CONTRACT_DTE}, {@Next_Quarter_End_Date})/365.25, 0)

#### CODE --> {@Noteable_Anniversary}

If      Remainder({@Age_Next_Querter},5) = 0
Then    'Y'
Else    'N' 

Any record where the final formula {@Noteable_Anniversary} = 'Y' will be the ones you want to report on, so suppress those where the result of the formula = 'N'.

Hope this helps.

Regards, Pete

### RE: show annivesary dates for next quarter

(OP)
Thanks so much Pete! This worked beautifully :)

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!