Leave Remaining Formula

Michelle Maas

I have an automated sheet for tracking annual leave requests. I use a formula (below) to track remaining leave. This formula recalculates and subtracts time taken as new requests are made for each employee.

=INDEX({Days Remaining}, MATCH(Primary6, {Employee}, 0))

However, on all the requests (rows), the column [Days Remaining] is tracking the OVERALL leave remaining, but I would like it to calculate the remaining leave TO DATE at the time of each request.

Can someone help me to edit the formula to achieve this?

Bassam Khalil

Hi @Michelle Maas

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Paul Newcome

A screenshot would be very helpful to see what you are working with. Feel free to remove, hide, or replace with mock data any confidential/sensitive data that shouldn't be shared.

Michelle Maas

Bassam Khalil

@Michelle Maas

please add a copy for the source sheet

Michelle Maas

Source Sheet

Paul Newcome

How are new entries made and which sheet are they made on?

Michelle Maas

They use a form that goes onto the Request Sheet which is linked to an Employee Information sheet where we track hire date, leave carried over, days taken and days remaining.

Paul Newcome

And which one of your screenshots is that?

Michelle Maas

Request Sheet (where the form submissions land)

Employee Information Sheet

Paul Newcome

OK. Just to make sure I have everything straight...

You need a formula on the Request Sheet that takes a total from the [Days Out] column from previous submissions and subtracts that from the the [Allotted Days] column for that employee so that you have a running balance remaining going down the sheet?

Michelle Maas

@Paul Newcome Yes that is what I am aiming for. Thanks

Paul Newcome

Great. So there are a few things we need to do and a question or two before we can really get the final solution built out...

Do we have something that is unique to each employee that we can match on? I see that you have "name" listed out in the Primary column which I assume would be the employee name, but do you have a unique ID for each employee so as to account for the possibility that there could be two employees with the same name?

Do you capture the "submitted date" by using a system generated Created (date) type column? If not, are new rows added to the top or the bottom of the sheet, and would you be open to inserting that column so that we can have some way of establishing which entries were prior to the current submission?

Michelle Maas

@Paul Newcome - no unique ID for employees on the form but we could use the employee number. If their names were the same, we would use a nickname or alternative name to avoid confusion in SS.

There is also an option to Submitted date is entered on the form and new rows are added at the bottom of the sheet. There is also an approval column that we could use to process the request that are a choice of 4 - Submitted / Approved / Declined / Cancelled, or trigger from the dates?

Paul Newcome

Ok. If the Submitted Date is captured and no two names will ever be the same then we should be able to get away with this:

=INDEX({Employee Info Sheet Allotted Column}, MATCH([Name Column]@row, {Employee Info Sheet Name Column}, 0)) - SUMIFS([Days Out]:[Days Out], [Submitted Date]:[Submitted Date], @cell <= [Submitted Date]@row, [Name Column]:[Name Column], @cell = [Name Column]@row, [Supervisor Approval]:[Supervisor Approval], @cell = "Approved")

Michelle Maas

Something is not working. I'm getting error - #UNPARSEABLE

To the Request Sheet (where the form submissions land), I have added a column - [Date Request Submitted] - system generated column.

To the Employee Information Sheet, I have added a column - [Total Annual Leave] - which adds together allotted and carry over days.

Formula amended below. Reference Links to other sheet are:

1. {Employee Information Range 5} = Total Annual Leave column.

2. {Employee} = Employee Information Primary column.

=INDEX({Employee Information Range 5}, MATCH([Primary]@row, {Employee}, 0)) - SUMIFS([Days Out]:[Days Out]), [Date Request Submitted]:[Date Request Submitted], @cell <= [Date Request Submitted]@row, [Primary]:[Primary], @cell = [Primary]1, [Supervisor approval]:[Supervisor approval], @cell = "Approved")