Percentage of year employed - prior year

[biggcc]

I trying to figure out how to make this query work. I have a simple database that is being used to show employee employment information - name, hire date, salary, bonuses, etc. Everything is just about done but they want me to show what percentage of the prior year the employee was there. In other words if an employee was hired 4/20/2004 they want me to show the percentage of 2004 they were employed with the company. I've tried just about everything I can think of but nothing seems to give me the right answer. I am also showing the percentage for the current year (2005) and that works ok. Just can't figure out how the calculate it for a prior year.

This is being done in a query and we're using Access 2000.

Any help would be greatly appreciated.

Thanks,

 

[Liv Manto]

check datediff function.

Calculate number of days from hire date to 1/1/2004 then divide by 365 days multiply by 100 (for the percentage)

 

[mresann]

Assuming your field in the query is called "StartDate", this query will return the percentage of the year worked for that particular date.

The key to calculating the percentage is the DatePart function. Setting the date interval unit to "Y" returns the day of YEAR. For instance, if the StarDate is #Apr 12 2004#, DatePart("y", [StarDate]) will return 103. It is interesting to note that the same date in a non-leap year is one less, or 102 (on all days following February 28).

Next, of course, we need the DatePart of the last day of the year. Using the DateSerial function, we create create the last day of the year for the StartDate, and use the DatePart on that date. For 2004, we return 366.

Finally, we perform the divison, formatting the result as a percentage using FormatPercent. To obtain the result for the time worked, we need to subtract this result from the Dec 31 result (either 365 or 366 depending on leap year).

Putting it all together in one line, we name this function "PctWorked" and put it in the query field.

PctWorked: FormatPercent((DatePart("y",DateSerial(Year(StartDate),12,31))-DatePart("Y",StartDate))/(DatePart("y",DateSerial(Year(StartDate),12,31))),0)

in the query will return 72%.

You can substitute StartDate for any datefield you wish.

 

[biggcc]

Thanks for the response. I think it's almost there but I'm trying to look for formula that's dynamic in that as the years change so do the percentages for each employee. Each year they want to look at this query and see how long each employee was here the year before as well as the current year. Here's an example:

Employee A - Startdate - 4/12/2004

For compsenation discussions for 2006 management needs to see on the query:

% Prior Yr. 72%
% Current Yr. 100%

For compsenation discussions for 2007 management needs to see on the query:

% Prior Yr. 100%
% Current Yr. 100%

And so on each year. This is basically so they can see how long the person was here the year before and this year and base their compensation results on that. Hope this explains it a little better. I'm going to continue to work with what you gave me and see if I can get it to work.

Thanks again for your response.

 

[mresann]

Let me see what you want in the query.

For compensation discussions for Year 2006 you need:

% Year 2004 Employed
% Year 2005 Employed

For compensation discussions for Year 2007 you need

% Year 2005 Employed
% Year 2006 Employed

Therefore, you have two query fields you need to populate, based on a third date field, the Compensation year. This value of this field should be Jan 1 of the year you want to use for the compensation.

Based on that information, here is one solution (I didn't verify it on my own Access table, so check if first):

PriorYear:
FormatPercent(
Iif (Year(Compensation)>Year(StartDate)+2, 1,
Iif (Year(Compensation)=Year(StartDate)+2, <<use earlier function WITHOUT FormatPercent>> ,
0)),0)

CurrentYear:
FormatPercent(
Iif (Year(Compensation)>Year(StartDate)+1, 1,
<<use earlier function WITHOUT FormatPercent>>
),0)

Does this help you?

 

[biggcc]

Thanks - I would have still been working on these without your help. I just need to tweak them a little bit and they should be perfect. These are the formulas that I ended up with:

%Prior: FormatPercent(IIf(Year(Date())>Year([StartDate])+2,1,IIf(Year(Date())=Year([StartDate])+2,((DatePart("y",DateSerial(Year([StartDate]),12,31)))-DatePart("y",[StartDate]))/(DatePart("y",DateSerial(Year([StartDate]),12,31))),0)),0)

%Cur: FormatPercent(IIf(Year(Date())>Year([StartDate])+1,1,(DatePart("y",DateSerial(Year([StartDate]),12,31)))-DatePart("y",[StartDate]))/(DatePart("y",DateSerial(Year([StartDate]),12,31))),0)

Maybe you can answer the couple of questions I have. On the second formula it returns a 0 (zero) percent for all employees that should be 100%. How can I change that?

Also if you notice I have replace your [Compensation] field with the Date() statement. Reason I did that was to prevent having to make another field in the table. But the question I have is how can I tell it to take the first day of the following year here? Right now it gives me the precentages but calculates them on the current year which is correct. Since they will be running these this year it needs to calculate the percentages based on the next year. i.e. for compensation 2006 percentages should reflect totals as of 1/1/2006.

Might of rambled on here some so if you have questions please let me know.

Thanks again for all your help!

 

[mresann]

My bad on the formula. In the FALSE condition of the Iif statement for the %Cur field, which is the last number in the statement, replace 0 with 1 (which will format to %100, instead of 0%).

To calculate the Compensation Year as Next year, use the following:

QUICK EDIT: Actually,

Year(DateSerial(Year(Now) + 1, 1,1)) returns the next YEAR by itsslf, ie, "2006"

DateSerial(Year(Now) + 1, 1,1) returns the first of January of next year, ie, "Jan 1 2006" (in whatever date format you have)