Leave Remaining Formula
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?
Answers
-
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.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
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.
-
please add a copy for the source sheet
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Source Sheet
-
How are new entries made and which sheet are they made on?
-
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.
-
And which one of your screenshots is that?
-
Request Sheet (where the form submissions land)
Employee Information Sheet
-
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?
-
@Paul Newcome Yes that is what I am aiming for. Thanks
-
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?
-
@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?
-
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")
-
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")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!