6 Replies Latest reply on Dec 5, 2016 11:24 AM by karen_joy

    Advice & Help (Calculation) for making a Vacation Tracker


      Hello Guys,


      I am planning to start my vacation tracker data base, but I don't know if I'm on the right track and How to calculate the vacation allowed days and remaining days


      I have Employee Table with the following Fields



      Employee Name

      Date Hire

      Vacation Allowed

      Vacation Taken

      Vacation Remaining


      How can I calculate the annual vacation base on the below condition


      Schedule: Regular full-time employee (40 hours per week)

      Years of Service

      *Maximum Annual Vacation Entitlement


      1 - 4

      10 days  (2 weeks)



      12.5 days


      5 - 9

      15 days  (3 weeks)



      18 days


      10 - 19

      20 days  (4 weeks)



      Thank you.

        • 1. Re: Advice & Help (Calculation) for making a Vacation Tracker

          First, your schedule needs to be a table in your database so that you can look up the "allowed" vacation days from it for a given employee.


          Next, I'd use a related table where a record related to your table of employees is created each time an employee takes vacation. You can compute total days taken from this related table to use in combination with the days allowed to compute the number of days remaining.


          Note that there are typically other details to this process not yet specified in your post--what happens to vacation days from previous years that were not taken? Your company's policies for this will determine whether you need to maintain a field where you keep a running total of all unused vacation days that spans more than the current fiscal year.

          • 2. Re: Advice & Help (Calculation) for making a Vacation Tracker

            Hi Phil,


            Thanks for the reply. Now, I have two Tables (Employee Table & Vacation Table)


            In my Employee table I have the following fields


            Employee_ID (Get UUID)




            Vacation Table

            Vacation_ID (Get UUID)







            Employee Table and vacation Table is connected with

            Employee_ID (Get UUID) = EmployeeID (Allowed creation ; delete related records)


            The unused vacation days for the current year will be forwarded next year.


            is this enough info to calculate the Days_allowed?


            Thank you.

            • 3. Re: Advice & Help (Calculation) for making a Vacation Tracker

              Good afternoon karen_joy,


              I hope your day is going well. I recently created a similar solution, and I thought it might be useful to list some of the considerations I had to account for during development:


              • Should total time be tracked in hours rather than days - my company permitted sick-time to be taken in 1 hour increments?
              • How do you account for varying work schedules?
              • How do you account for vacation requests that span non-workdays (e.g., weekends, holidays, etc...)?
              • How to automagically account for "floating" company holidays (e.g., Easter, Thanksgiving, etc...)?
              • How to track time off request status (e.g., approved, denied, approved without pay, pending, etc...)?
              • Does paid time off (PTO) reset for everyone on the same date, or is it based on hire date anniversary?
              • If the PTO reset date is common for all employees, how is the prorated time for the first year handled?


              This is not an exhausted list, but if I had possessed the wisdom you demonstrated in reaching out to this community before beginning my project, and someone had provided a similar list for me, I would have more hair today. Good luck!


              God bless,




              • 4. Re: Advice & Help (Calculation) for making a Vacation Tracker

                Hi Bill,


                All is well. Thank you for asking & Thank you for the list, May be as I go on, I will be going back to your list but as of now, I want to get help to calculate the vacation allowed base on their year of service.


                Thank you.

                • 5. Re: Advice & Help (Calculation) for making a Vacation Tracker

                  I would put the days allowed and days remaining in the employee table, not the vacation table.

                  • 6. Re: Advice & Help (Calculation) for making a Vacation Tracker

                    Thanks for the suggestion Phil