Help with a formula

Hi All,

I’m trying to create a base for managing and tracking my time off requests. The service we use is clunky and difficult to interpret, so I want to keep track of this on my own in the event the service we use does not line up to what I requested. I’m having some difficulty on how to do a formula or at least achieve a particular end result.

I have a table called Time Off Benefit

Time Off Benefit Table

I next have created a table for my time off request to include the reason, a column for Type (vacation, sick, remote in and bonus hours), Hours requested and # days (which is hours requested divided by 8 hours), start date, finish date, approved (yes no) and approval date.

What I now want to do is figure out a way in another table to calculate time left for each time off type based on the benefit table for the given work year

My work year is equal to date of hire…so I was hired on 3/11, therefore my time starts on 3/11 and ends on 3/10 of the next year with my time restarting on 3/11

In the Time Off Benefit Table I added a column called 2020

I want to achieve something like this for year 2020 by way of a formula where the formula looks at the entire Time Off Requests table and drops a total into a particular cell for each Time off Type:

If start date is between 3/11/2020 and 3/10/2021 and Time off type is Vacation, then calculate #hours requested (from Time Off Table)

Repeat for Person/Sick
Repeat for Remote In
Repeat for Bonus Hours

I’m struggling with…

a)can this be done?

b)how should I structure the table that will do this? ie columns, rows etc

Any help or guidance on how to get there or structure my table would be appreciated.
Emma

I use Airtable to track my vacations, as well as my time-off requests for work. I got my base to automatically calculate the number of vacation, PTO, and sick leave hours I’ve accrued based on my hire date. The base also subtracts the amount of hours I’ve logged.

I would suggest copying this base and playing around with it. In the [Leave Balance] table, adjust the {Accrual Begins} field and the {Hours Per Year} field to match your company’s policy.

The default value of hours taken with every leave request is 8 hours, but the base will subtract an exact amount if I give it one.

A caveat: my base calculates leave balance totals, not by year.