Discussions

Expand all | Collapse all

Need assistance with a formula for calculating annual vacation days that automatically roll over any unused days the following year.

  • 1.  Need assistance with a formula for calculating annual vacation days that automatically roll over any unused days the following year.

    Posted 5 days ago

    I have an employee table (parent) that includes a "Benefits Eligibility Date (Start Date plus 90 days)." I have a PTO table (child) that has a multiple choice field "Leave Request Type (Vacation, Sick, Professional Development)," a numerical field "# of OOO Days Requested," and a multiple choice field "Approved/Not Approved." From the Benefits Eligibility Date employees are eligible for 10 business days of vacation annually (not calendar or fiscal year but within the year after they started - Eligibility Date). If # of 000 Days Requested are equal to Vacation and Approved, on the employee table I want to be able to calculate the total number of approved vacations days during the current year, another field for the unused available days for the current year, and then after the anniversary Benefits Eligibility Date is passed for the current year the number of vacation days for the following year calculates the 10 vacation days for the next year plus unused vacation days from the previous year.

     

    Does that make any sense?

     

    Another caveat - I need some sort of "override" for those that may negotiate something different into their employment contract.

     

    I've written some doozy of formulas before but I am stumped on this one. Any thoughts?



    ------------------------------
    Dawn Rene
    ------------------------------


  • 2.  RE: Need assistance with a formula for calculating annual vacation days that automatically roll over any unused days the following year.

    Posted 4 days ago
    Dawn,

    Add a table for PTO Types so that you can add days of PTO that are Earned or Bonused for the Employee.  

    ERD

    Now you can use Summary Fields at the Employee table to calculate the remaining days of PTO that the Employee has

    Total Available = SUM [Days]  where Related PTO Type = Earned 
    Total Approved = SUM [Days]  where Related PTO Status = Approved

    Total Remaining = Total Available - Total Approved


    Now you can add additional PTO Types and PTO Status to meet the evolving HR model.   

    I have a client that awards PTO to employees when they go on cross country trips and stay over the weekend.  It saves money on airline travel, so they get bonus PTO.   

    You have Earned PTO added on the Anniversary date of Employment.

    And so on and so forth to meet the complex HR cases that come up.

    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------