Calculating Employee Holidays Due - Very Complicated!!

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
All,

OK, I'll give this a go and if I leave anything out please post back with questions.
I've been putting this on hold for oooo 8 months.

The basics are that I need to calculate Employee holidays based on LOTS of factors.

Here are the key elements:

tbl_EmployeeMain

1) StartDate - the date the employee started to work for us.
2) LeavingDate - the date the employee stopped working for us.

tbl_LeaveYearEnd

3) LeaveYear - This is the date that an employee's leave year ends
Note: this is generally 30th November, however they can opt for a different date

tbl_EmployeeHours

4) HoursStartDate
Note: If it is the Employees 1st hours pattern then the HoursStartDate will be blank and the tbl_EmployeeMain!StartDate is used
5) HoursEndDate
Note: is blank if they leave, tbl_EmployeeMain!LeavingDate is used

The above is the elements I currently have, I have worked out how to calculate holidays on the above, but the next part throws it all off.

------------------------------------------------------------------

Holidays are calculated like this:

(([Days]/365)*[Hours]*22*7.4)/37

Green = Constants, always those numbers
Purple = Calculated
Orange = The hard part - is how many days holiday someone is entitled to pro-rata per annum.

Days (Orange) a year are work out like this:

0 - 1 year service = 22 days i.e. 1st Jan 03 - 31st Dec 03
1 - 10 years service = 25 days using above 1st Jan 03 - 1st Jan 04 = 365 days @ 22 and 1 day @ 25
10 + = 30 days

So using the above info:

Jim Brown Started 27th Oct 03 and works 25 hours during training for 2 weeks,
on 10th Nov after training is put up to fulltime at 37hrs p/w,
Jim then has a baby to look after and drops his hours to 20 p/w starting Mon 2nd Feb 04 then again to 10p/w on 29th Oct 04, he is due to leave 29 Apr this year.

Basically it should be:

Year Decimal Hours Hours
Leave 03 - 13.58575342 - 13:35
Leave 04 - 107.1484932 - 107:08
Leave 05 - 20.54794521 - 20:32

I realise the above may not make sense so question are welcome.
I have attached the raw DB with what I have, I'm not even sure this is a Query Question or I could use a function.

Oh, here's the other thing.....................Jim doesn't get to see his service balance until he has completed his full year, i.e. the projected hours will be on 22 until 28th Oct then he will se it at 25.

I REALLY hope I can get this monkey as it's driving me nuts.

P.S. This needs to be done in Access 97, cheers.

-----------

Just thought of something else,
An employee may have previous service.

I can add a table which has this in days but it will need to be calculated in.

SO, Jim used to work for us from 1st Nov 88 - 22nd Jul 92.

His service is 1360 days, this can be stored in a seperate table.

BUT! that does mean his service year is now 18 Jul (I think, based on quick calculations and NOT having spoken to HR about this)

I can email my starting point DB to anyone who wants a stab.

Se below for expected outcomes from a Query.

Cheers all who contribute.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174

Forum statistics

Threads
1,148,053
Messages
5,744,528
Members
423,881
Latest member
Nguyen Vu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top