Group records with custom yearly grouping

Hi Experts,

I am creating a report where I need to group records yearly but it needs to depend on a specific date.
More in detail, my report is pulling employee records for their hours paid.
I need to show a total of hours paid yearly on the date he/she was hired

So if an employee was hired on 7/1/2010 then it needs to group and total as follows:

Range                                    Total hours paid
======                                  ===============
7/1/2010 - 6/30/2011                    456
7/1/2011 - 6/30/2012                    789
7/1/2012 - 6/30/2013                  1012

If you could guide me to the proper grouping formula I need to create I would really appreciate it

Thank you

Who is Participating?
mlmccConnect With a Mentor Commented:
Try a formula like this

Local DateVar SaleYearStart := Date({Employee.Hire Date});
Local DateVar SaleYearEnd := Date(DateAdd('yyyy',1,{Employee.Hire Date}) - 1);
While SaleYearEnd < {Orders.Order Date} do
    SaleYearStart := Date(DateAdd('yyyy',1,SaleYearStart));
    SaleYearEnd := Date(DateAdd('yyyy',1,SaleYearEnd));

CStr(SaleYearStart,"M/d/yyyy") &  " -  " & CStr(SaleYearEnd,"M/d/yyyy")

Open in new window

How many years are you looking to see?

carlosmonteAuthor Commented:
since the employee started, so date of hire
if they started in 1999 then from 1999 to today
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

carlosmonteAuthor Commented:
Seems to be working perfectly :)
I will test thoroughly and get back to you asap

Martin LissOlder than dirtCommented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for mlmcc's comment #a39291633

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
carlosmonteAuthor Commented:
The solution has worked great.
Thank you mlmcc!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.