Date conditional

[EduPAz]

Hi,

Can someone please help me with this formula?

I have this info:
Column A = employee name
Column B = hiring day
Column C = Original vacation days per year (15 or 20)
**Column D = Xtra vacation days
Column E = Total vacation days (15+column D)

For every year worked after 4 years, the employee gets an extra day:

For 5 years, the employee gets 1 extra day. Total vacation days : 15+1
For 6 years, the employee gets 1 extra day. Total vacation days : 15+2
For 7 years, the employee gets 1 extra day. Total vacation days : 15+3
For 8 years, the employee gets 1 extra day. Total vacation days : 15+4
For 9 years, the employee gets 1 extra day. Total vacation days : 15+5

The maximum number of vacation days is 20. If the original vacation days per year (Column c) is 20, the extra day policy is not applied.

Thanks,

Edu

 

[ClaireS]

In column D put

[FONT=Verdana]=INT(YEARFRAC(B2,TODAY()))-4[/FONT]

This will calculate the "extra" number of days (though with new employees, this will start at -4 to give them the 5 years before they get an actual extra day!).
In column E, use the formula

[FONT=Verdana]=IF(C2+D2>=20,20,IF(D2<=0,C2,C2+D2))[/FONT]

This will add extra days if they have not already reached the maximum of 20 but leave the days unchanged if they have 0 or fewer extra days.

 

[EduPAz]

Hi,

Thank you for your answer. How would be if my initial statement change in this way:


Column A = employee name
Column B = hiring day
Column C = Vacation days per year (15, 16, 17, 18,19 or 20) --> This is the number of vacation days that every employee has at January 1st 2019. For example, some employees have 17 days because at January 1st 2019, they have worked for 6 years.
**Column D = Xtra vacation days for the year (at today) --> The value would be 1 or 0. If the employee earns an extra day during the year (the employee complies the condition of year worked), put 1.
Column E = Total vacation days at today (column C + column D)

For every year worked after 4 years, the employee gets an extra day:

For 5 years, the employee gets 1 extra day. Total vacation days : 15+1
For 6 years, the employee gets 1 extra day. Total vacation days : 15+2
For 7 years, the employee gets 1 extra day. Total vacation days : 15+3
For 8 years, the employee gets 1 extra day. Total vacation days : 15+4
For 9 years, the employee gets 1 extra day. Total vacation days : 15+5

The maximum number of vacation days is 20. If the original vacation days per year (Column c) is 20, the extra day policy is not applied.

Thanks,

Edu

 

[ClaireS]

Try this:

	A	B	C	D	E
Name	Hire date	Days at 1/1	Extra this year	Total days
Fred
Joe
Bert
Mary
Sue
Jill

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]23/08/2015[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]21/12/2010[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]19[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]03/05/2011[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]19[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]30/08/2006[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]14/03/2013[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]12/06/2000[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10"]C2[/TH]
[TD="align: left"]=IF(YEARFRAC(B2,TODAY())<5,15,IF(YEARFRAC(B2,TODAY())>=9,20,15+INT(YEARFRAC(B2,DATE(YEAR(TODAY()),1,1)))-4))[/TD]
[/TR]
[TR]
[TH="width: 10"]D2[/TH]
[TD="align: left"]=IF(TODAY()>=DATE(YEAR(TODAY()),MONTH(B2),DAY(B2)),1,0)[/TD]
[/TR]
[TR]
[TH="width: 10"]E2[/TH]
[TD="align: left"]=C2+D2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

In the final argument of C2, I start with 15 and subtract 4, as this reflects the way you work, but you could obviously change it to start with 11 and not subtract anything.

 

[EduPAz]

Hi,

Thank you for your answer.
For column D, the formula is not capturing the condition:

For every year worked after 4 years, the employee gets an extra day:

For 5 years, the employee gets 1 extra day. Total vacation days : column c+column d
For 6 years, the employee gets 1 extra day. Total vacation days : column c+column d
For 7 years, the employee gets 1 extra day. Total vacation days : column c+column d
For 8 years, the employee gets 1 extra day. Total vacation days : column c+column d
For 9 years, the employee gets 1 extra day. Total vacation days : column c+column d

Total vacation days can not be greater than 20. If the employee already has 20 days in column c, the value in column d is zero

Edu