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
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
