How to create formula for leave count

[jcien86]

Hi All,

I'm doing the leave count but don't know how to create the formula..

For example:

Sep'09 to Aug'10 - every month:
1/12*14 | 2/12*14 | 3/12*14 | 4/12*14 | ... ... 12/12*14

Sep'10 to Aug'11 - every month:
1/12*15 | 2/12*15 | 3/12*15 | 5/12*15 | ... ... 12/12*15

Sep'12 to Aug'13 - every month:
1/12*16 | 2/12*16 | 3/12*16 | 5/12*16 | ... ... 12/12*16

how should it be done? any kind soul can teach?

Thanks ..

 

[pbornemeier]

I do not understand "leave count".
What action does the pipe command (|) represent in your post?
Please provide a more detailed explanation of what you are starting with and what you want to end up with.

 

[jcien86]

Sorry for my bad explanation.

I'm doing for my company annual leave count for all the employee.

every employee who works in the company for
0 - 2years entitle 14 annual leave per yr
3 - 4years entitle 15 annual leave per yr
4 - 6years entitle 16 annual leave per yr
7 - 8years entitle 17 annual leave per yr
9 - 10years entitle 18 annual leave per yr

Example:
AL = Annual leave

Employee A start working on 1/1/2008

Formula D
1 Jan'09 =1/12*14 --> 1.17 AL
2 Feb'09 =2/12*14 --> 2.33 AL
3 Mar'09 =3/12*14 --> 3.5 AL
4 |
5 |
6 |
7 |
8 V
9 Dec'09 =12/12*14 --> 14 AL
10 Jan'10 =1/12*14+D10 -->15.17 AL
|
|
|
|
V
20 Jan'11 = 1/12*15+D19 --> xx AL

Is there any formula that can do it more easily ?

Thanks

 

[pbornemeier]

A few questions to pin down boundary conditions:

If someone starts work on January 1 and someone else starts Jan 15,
1) Did either of them earn any leave for January?
2) If they did, do they earn the same amount?
3) If they earn leave and it is not the same amount, how is that leave calculated?

4) Is leave earned during the month? Today is 5/26, has anyone earned any leave for May?

5) For employees with more than 10 years, do they still earn 18 days per year?

6) If an employee gets to the 4 year point in the middle of the month, do they earn 14, 15, or 14 1/2 days for that month)