# Add days off to employees as time goes by

• 12-06-2018, 07:30 PM
fabian_76
Add days off to employees as time goes by
Hi Folks,

I have in ColA a drop drown menu to select employees. In ColB is their Start Day with the company. In ColC I would like to add a code that calculates their days off they have up to date. The company gives them 14 days per year.

Thanks,
• 12-06-2018, 08:34 PM
rcm
Re: Add days off to employees as time goes by
Please send a non sensitive sample...
• 12-07-2018, 12:20 PM
fabian_76
Re: Add days off to employees as time goes by
Thank you, please see attached file.
• 12-07-2018, 12:27 PM
Re: Add days off to employees as time goes by
Hola fabian_76,

How accurate do you want the calculation to be? In your example you mention 18 months... so should each COMPLETE month count as 1 12th of the 14 days? Or does it need to be calculated based on days?

Your example also says that Luis has a complete year with the company... but that is not really true if today is not yet Dec 15... do you want Dec 15 to count as if it was Dec 1?

This formula would give you Luis' vacation days based on the number of COMPLETE months between Hire Date and Today:

Formula:
`=DATEDIF(B2,TODAY(),"m")*(14/12)`
• 12-07-2018, 12:35 PM
rcm
Re: Add days off to employees as time goes by
Here is the file received with the formulas included. Excel stores dates in a way that one can add or substract them easily. I assumed that vacation was given for round years only
• 12-07-2018, 12:37 PM
fabian_76
Re: Add days off to employees as time goes by
Or does it need to be calculated based on days? YES, based on days.
do you want Dec 15 to count as if it was Dec 1? I want to consider 15 days when saying Dec 15.

Actually I made wring calculations, but the goal is to give the employee the opportunity to enjoy a day off once they have it. Also if the formula can tell the employe "As of today you have 3/4 days" and in an adjacent column something like this " In order to have a complete day, you must wait until XXXX date"

Thanks
• 12-07-2018, 12:57 PM
Re: Add days off to employees as time goes by
Days is a bit trickier since not all years have an identical number of days (leap year), but basically this should calculate available vacation to 2 decimal places:

Formula:
`="As of today you have "&ROUND(DATEDIF(B3,TODAY(),"d")*(14/365),2)&" days."`

And since 26 days is not enough for a full vacation day, it will have to be 27 days which is 1.0356 vacation days:

Formula:
`=IF(ROUND(DATEDIF(B3,TODAY(),"d")*(14/365),2)<1,"In order to have a complete day you must wait until "&TEXT(B3+27,"mmmm d, yyyy"),"")`

Formulas are written for row 3, which is Luis in your example.
• 12-07-2018, 06:41 PM
fabian_76
Re: Add days off to employees as time goes by
Hi @Arkadi. The formula worked great if the employee started on the 2017. However If an employee started working with us on 7/15/2016 , the formula says e.g. " In order to have a complete day you must wait until 8/12/2017" which does not make sense.
• 12-11-2018, 01:13 PM