Add days off to employees as time goes by

[fabian_76]

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,

[rcm]

Please send a non sensitive sample...

[fabian_76]

Thank you, please see attached file.

[Arkadi]

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:

Please Login or Register  to view this content.

[rcm]

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

[fabian_76]

Hola thanks for your quick answer. Here my answers:
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

[Arkadi]

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Formulas are written for row 3, which is Luis in your example.

[fabian_76]

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.

[Arkadi]

@fabian_76, that is strange indeed. I can't reproduce the problem though... if I put 7/15/2016 the formula results in a blank since that date would mean lots of vacation built up.

[fabian_76]

thank you Arkadi!

[Arkadi]

You are most welcome... but do you still have the 2016 problem? I'm happy to look in more detail, I just haven't been able to duplicate the problem. If you do still have an issue, please attach a workbook with an example of the problem and I will see what I can find?