Home |
Search |
Today's Posts |
#1
|
|||
|
|||
excell
Hello,
Iam trying very hard to find a fourmula in order to calculate holidays for my employees. The problem is as follow... If they are in the company for less than 2 years they are entitle to 20 days between the 1st of January and the 31st of december If they are in the company between 2 and 5 years they are entitle to 22 days from their anniversary date. If they are in the company for more than 5 years they are entitle to 25 days from their anniversary date. My problem is to take in consideration their anniversary date (ex starting date the 15 of march 2000 how many holidays does this person get?) If anyone could get back to me on this one I will be very gratfull : Here is the formula I have for the 20 days =((((IF(C7<0,IF(YEAR(C7)<YEAR(NOW()),20,ROUNDUP(1 .66*(12-MONTH(C7)+(IF(MONTH(C7)<2,IF(MONTH(C7)7,IF(MOD(M ONTH(C7),2)0,30,31),IF(MOD(MONTH(C7),2)0,31,30)) ,(IF(MOD(YEAR(C7),4)0,28,29)))-DAY(C7)+1)/IF(MONTH(C7)<2,IF(MONTH(C7)7,IF(MOD(MONTH(C7),2) 0,30,31),IF(MOD(MONTH(C7),2)0,31,30)),(IF(MOD(YE AR(C7),4)0,28,29)))),3)),0)))*A7)-Q7)-((((IF(V7<0,IF(YEAR(V7)<YEAR(NOW()),20,ROUNDUP(1. 66*(12-MONTH(V7)+(IF(MONTH(V7)<2,IF(MONTH(V7)7,IF(MOD(M ONTH(V7),2)0,30,31),IF(MOD(MONTH(V7),2)0,31,30)) ,(IF(MOD(YEAR(V7),4)0,28,29)))-DAY(V7)+1)/IF(MONTH(V7)<2,IF(MONTH(V7)7,IF(MOD(MONTH(V7),2) 0,30,31),IF(MOD(MONTH(V7),2)0,31,30)),(IF(MOD(YE AR(V7),4)0,28,29)))),3)),0)))*A7)) :lol: :lol: :lol: :lol: :lol: :lol: :lol: |
#2
|
|||
|
|||
Good morning, Catherine-
Will a slightly more simplistic view work? I mocked up a formula with three columns: a header in A2 that says "Start date"; a header in B2 that says "Employed Years", and a header in C2 that says "Vacation days". In A3 I used your sample date, March 15 2000. I used this formula in B3: =(TODAY()-A3)/365 .... which generates a fractional number of years the employee has worked. This is the oversimplification: it assumes 365 days in every years, which is not always the case but arguably has little impact on the intent of the calculation. The formula in C3 is =IF(B3<2,20,IF(AND(B3=2,B3<=5),22,IF(B35,25,"")) ) .... which tells the allowed number of vacation days. Does that do it for you? |
#3
|
|||
|
|||
Catherine,
I am making some assumptions: If they are in the company for less than 2 years they are entitle to 20 days between the 1st of January and the 31st of december I assume that, as in my company, only whole years between 1st January and 31st December are counted. This means that in the first part year, (in your example between 15th March until the 31st December you get a proportion,(0.6 days) for every month - 15 days in the first part year. After that all holidays are calculated assuming that the employee will be employed until 31st December. Therefore in your example in the year 2001 the employee would be due 20 days. To simplify the formula I used a helper cell, (E1), to calculate the number of complete years for use in the calculation. If you do not want to use a helper cell then you will have to replace E1 the formula with the formula in Cell E1. The employee's start date is in D1 and the formula to calculate the holidays due is: =IF(E1=0,INT(DATEDIF(D1,DATE(YEAR(D1),12,31),"m")/12*20),(E10)*20+(E1=2)*2 +(E1=5)*3) HTH Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk "CATHERINE" wrote in message ... Hello, Iam trying very hard to find a fourmula in order to calculate holidays for my employees. The problem is as follow... If they are in the company for less than 2 years they are entitle to 20 days between the 1st of January and the 31st of december If they are in the company between 2 and 5 years they are entitle to 22 days from their anniversary date. If they are in the company for more than 5 years they are entitle to 25 days from their anniversary date. My problem is to take in consideration their anniversary date (ex starting date the 15 of march 2000 how many holidays does this person get?) If anyone could get back to me on this one I will be very gratfull : Here is the formula I have for the 20 days =((((IF(C7<0,IF(YEAR(C7)<YEAR(NOW()),20,ROUNDUP(1 .66*(12-MONTH(C7)+(IF(MONT H(C7)<2,IF(MONTH(C7)7,IF(MOD(MONTH(C7),2)0,30,3 1),IF(MOD(MONTH(C7),2)0,3 1,30)),(IF(MOD(YEAR(C7),4)0,28,29)))-DAY(C7)+1)/IF(MONTH(C7)<2,IF(MONTH(C7 )7,IF(MOD(MONTH(C7),2)0,30,31),IF(MOD(MONTH(C7), 2)0,31,30)),(IF(MOD(YEAR( C7),4)0,28,29)))),3)),0)))*A7)-Q7)-((((IF(V7<0,IF(YEAR(V7)<YEAR(NOW()),20, ROUNDUP(1.66*(12-MONTH(V7)+(IF(MONTH(V7)<2,IF(MONTH(V7)7,IF(MOD(M ONTH(V7), 2)0,30,31),IF(MOD(MONTH(V7),2)0,31,30)),(IF(MOD( YEAR(V7),4)0,28,29)))-DAY (V7)+1)/IF(MONTH(V7)<2,IF(MONTH(V7)7,IF(MOD(MONTH(V7),2) 0,30,31),IF(MOD(M ONTH(V7),2)0,31,30)),(IF(MOD(YEAR(V7),4)0,28,29) ))),3)),0)))*A7)) :lol: :lol: :lol: :lol: :lol: :lol: :lol: |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I view an excell document without excell (not installed wit. | Excel Discussion (Misc queries) | |||
Can I view an excell document without excell (not installed wit. | Excel Discussion (Misc queries) | |||
lable ranges in Excell | Excel Discussion (Misc queries) | |||
Why can't we see or access MS Excell 2003 even though we have MS . | Setting up and Configuration of Excel | |||
EXCELL 2002 Glitch???? | Excel Discussion (Misc queries) |