+ Reply to Thread
Results 1 to 8 of 8

Add days off to employees as time goes by

  1. #1
    Forum Contributor
    Join Date
    03-14-2015
    Location
    Mexico
    MS-Off Ver
    2013
    Posts
    124

    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,

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,311

    Re: Add days off to employees as time goes by

    Please send a non sensitive sample...

  3. #3
    Forum Contributor
    Join Date
    03-14-2015
    Location
    Mexico
    MS-Off Ver
    2013
    Posts
    124

    Re: Add days off to employees as time goes by

    Thank you, please see attached file.
    Attached Files Attached Files

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    3,892

    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: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Arkadi; 12-07-2018 at 12:29 PM.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  5. #5
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,311

    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
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    03-14-2015
    Location
    Mexico
    MS-Off Ver
    2013
    Posts
    124

    Re: Add days off to employees as time goes by

    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

  7. #7
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    3,892

    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: copy to clipboard
    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:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


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

  8. #8
    Forum Contributor
    Join Date
    03-14-2015
    Location
    Mexico
    MS-Off Ver
    2013
    Posts
    124

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1