Leave Remaining Formula

Paul Newcome

You have an extra parenthesis tucked in after the [Days Out] column reference (first range in the SUMIFS function). Try this with it removed...

=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]@row, [Supervisor approval]:[Supervisor approval], @cell = "Approved")

Michelle Maas

Okay that's great, it has removed the error. Now I want it to calculate correctly. Seems to be a step by step process but we're getting closer! 😃

{Employee Information Range 5} = Total Annual Leave column = 25

1st request (Days Out = 4) should be 21, 2nd request (Days Out = 1) should be 20, then as each new request comes in, the Remaining Leave is reduced by the Days Out column.

Calculation for 1st request is showing as Remaining Leave = 20 and if I copy formula down, 2nd request is also 20. How do I fix this?

Paul Newcome

What are the dates in the Submission Date column?

Michelle Maas

They vary as they are the date that the request has been made.

Paul Newcome

I understand they will vary because of when the submission is made, but it seems as if it is either not registering the dates in the formula or the dates are the same for the specific rows you mentioned in your last post. What are the dates in those rows you are referring to where there is an issue?

Michelle Maas

The system adds the dates with the time i.e. 17/06/21 17:23. Yeah some of the dates are the same.

Paul Newcome

The rows that have the same output... Are they for the same person and submitted on the same date?

Michelle Maas

No, different people

Paul Newcome

I thought you were trying to calculate for each person?

In your comment here:

"1st request (Days Out = 4) should be 21, 2nd request (Days Out = 1) should be 20, then as each new request comes in, the Remaining Leave is reduced by the Days Out column.

Calculation for 1st request is showing as Remaining Leave = 20 and if I copy formula down, 2nd request is also 20. How do I fix this?"

Is that for the same person, or for different people? If for different people, is it a "shared" total amount of time?

Michelle Maas

Yes, the calculation is per employee. No one shares their annual leave. 😅

Paul Newcome

Ok. So in this comment here...

"1st request (Days Out = 4) should be 21, 2nd request (Days Out = 1) should be 20, then as each new request comes in, the Remaining Leave is reduced by the Days Out column.

Are those two rows for the same person or different people and are they submitted on the same date or different dates?