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

    Advice & Help (Calculation) for making a Vacation Tracker

    karen_joy

      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_ID

      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)

       

      4-5

      12.5 days

       

      5 - 9

      15 days  (3 weeks)

       

      9-10

      18 days

       

      10 - 19

      20 days  (4 weeks)

       

       

      Thank you.

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

          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
            karen_joy

            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)

            Employee_name

            Date_hire

             

            Vacation Table

            Vacation_ID (Get UUID)

            EmployeeID

            Start_date

            End_date

            Days_allowed

            Days_remaining

             

            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
              BillisSaved

              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,

               

               

              Bill

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

                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
                  philmodjunk

                  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
                    karen_joy

                    Thanks for the suggestion Phil