Vacation Request Form & Balance Monitoring

[lucas__mar]

Hello there! 

I've built a Base to track "Vacation Requests" and 'balance vacation days' in two simple tables:

1) Staff: where fields are name, email, role, hired date, accrued days (formula based on hired date), used days (roll up from the sum of all approved vacation requests), balance days (accrued-used).

2) Vacation Request: where fields are req. id (formula), name (linked from staff table), email (linked from staff table), start-date, end-date, requested days (formula), status, duration (formula, only populate if status approved, this field is used on a roll up in the 'staff' table). 

Up to there, everything's perfect. Where I'm stuck is how to keep track once the 12 month period is reset for each team member. Picture below illustrates an example: 

Technically, for that team member that used 27 days already, now "his" calendar starts all over again, and he now should have 28 days available. Since all team members have different "hired dates" this is where I am a bit stuck. If I keep using the "vacation request form" then formulas will simply add days and show negative balance more and more. Don't know if I'm explaining myself correctly, hopefully I am hehe. Anyone has an idea on how to solve this? Cheers!

[Dan_Montoya · Accepted solution]

Hi Lucus, it sounds like each staff member has an hire anniversary date and you only want to count vacation requests that in the current year to deduct from the 28 each staff member accrues each year.  You will need a few helper fields to do this:

STAFF -> hire day and month (formula with datetime_format on your hire date field)

STAFF-> vacation year start and vacation year end fields.  These are your hire day and month with the current year on the start and the next year on the vacation year end (unless they were hired on 1/1).

VACATION Requests-> use the lookup fields to pull in the vacation start and end dates.  new field "In current Vacation Year" if the dates in the request are > vacation start  and < vacation end it is a current year.  

Then in your roll up of days used add the filter that it is in current year.

[lucas__mar]

Hey there Dan! 
Thanks for the suggestion! I was super stuck! I've added a few extra details to your approach, in order to make the whole system "Dynamic" so it's always recognizing the "Current" period for each staff member. This way 'Vac Yr Start' is 'Current Vac Yr Start' using this formula:

IF(

    DATEADD({Hired Date}, YEAR(TODAY()) - YEAR({Hired Date}), 'years') > TODAY(),

    DATEADD({Hired Date}, YEAR(TODAY()) - YEAR({Hired Date}) - 1, 'years'),

    DATEADD({Hired Date}, YEAR(TODAY()) - YEAR({Hired Date}), 'years')

)

And 'Vac Yr End' is 'Current Vac Yr End' using this formula:

DATEADD({Current Vacation Year Start}, 1, 'year')

Then I adjusted the 'In Current Vac Yr" approach you suggested, using this formula:

IF(

    AND(

        {Start Date} >= {Current Vacation Year Start},

        {Start Date} < {Current Vacation Year End}

    ),

    "TRUE",

    "FALSE"

)

And everything seems to be working smoothly. Can't thank you enough for the brains and the time! Hope more users can benefit from this! It's an easy way out to manage vacation requests for small teams as in our organization (20 max). Cheers!

[TheTimeSavingCo]

Hmm, you could also try:

In your 'Vacation Request' table, add a formula field that'll check whether the request is made for that year

After that, in your rollup field for "Used Days", add a conditional so that it'll only use records where the vacation request was made for that year

This should do what you want I think?  If someone applies for a vacation from 31 Dec - 5 Jan this system wouldn't be able to handle it though