Calculating Remaining Eligible Vacation Days with already Approved / Declined Requests

ichen

Hi there,

Is it possible to do the following in SS?

I'm currently working with two sheets, Leave Requests and Employee Directory.

• My Leave Requests is the main sheet I'm working with - a running list of all leave requests from our staff
• My Employee Directory is a reference sheet for the number of annual vacation days an employee is eligible based on their length of service at our company

In my Leave Requests sheet, I have two scenarios, based on three statuses that I currently have on my sheet: Approved, Declined, Submitted

Scenario 1:

• If a leave request is Approved / Declined, and the employee already has existing requests on my sheet (regardless of status), I want to take the annual number of vacation days (from my Employee Directory) sheet, and minus all the already approved vacation days to calculate the employees' remaining vacation days

Scenario 2:

• If a leave request is Submitted, leave my remaining vacation days blank until it is Approved / Declined, then use calculation from Scenario 1  

Does my explanation above make sense?

If this is possible, perhaps someone can help me out with the formula? I'm struggling with determining how to add all the already approved vacation days for a specific individual.

Thank you!! 

aafek

Hi! Yes, it's possible. You'll have to use SUMIFS formula with 2 criteria and reference the other smartsheet to get the number of days requested. I cannot attach a smartsheet onr Excel here, but I've created a similar solution in Excel attached a screenshot (only jpg/png allowed). Smartsheet also uses the SUMIFS formula, so after a small modification you should be able to adapt it to your needs. Hope this helps!

Paul Newcome

=IF(Status@row = "Submitted", "", INDEX({Employee Directory Total Vacation Time Column}, MATCH([Employee Name]@row, {Employee Directory Name Column}, 0)) - SUMIFS([Leave Requested]:[Leave Requested], [Employee Name]:[Employee Name], [Employee Name]@row, Status:Status, "Approved"))

.

The first portion says that if the Status is "Submitted", leave the cell blank.

.

The second portion of this (INDEX/MATCH) will look at the employee name in the row of the formula and pull the corresponding value from your Employee Directory sheet.

.

minus

.

The third portion of this (SUMIFS) will add up all of the values in your leave requested column that match BOTH the employee name in the same row as the formula as well as the status being "Approved". Below is a breakdown...

.

Status@row: Refers to the the column in your Leave Request sheet housing the approval status. You can leave the "@row" portion as that just tells the formula to look at the specified column in the same row as the formula.

{Employee Directory Total Vacation Time Column}: Use a cross sheet reference to set the range as the column in your Employee Directory sheet that houses the number of vacation days the employees are eligible for.

[Employee Name]@row: Refers to the column in your Leave Request sheet that houses the employee's name. Leave the "@row"

{Employee Directory Name Column}: Refers to the column in your Employee Directory sheet that houses the employee's name. Use cross sheet references to define the range.

[Leave Requested]:[Leave Requested]: The column that houses the values for the hours requested for each submission on your Leave Requests sheet.

[Employee Name]:[Employee Name]: Same as [Employee Name]@row above except refers to the entire column.

Status:Status: Same as Status@row above except refers to the entire column.

ichen

Hi Paul, 

I tried using using the formula you provided, unfortunately I'm receiving an #Invalid Operation error. 

I can confirm that the first and second portion of the formula works with no issues: 

=IF(Status2 = "Submitted", "", INDEX({Employee Directory Range 1}, MATCH([Email Address]2, {Employee Directory Range 4}, 0)))

However, when I add the third portion of the formula, it seems to throw the error. Perhaps I missed something in my formula, which currently looks like this: 

=IF(Status2 = "Submitted", "", INDEX({Employee Directory Range 1}, MATCH([Email Address]2, {Employee Directory Range 4}, 0)) - SUMIFS([Vacation Leave]:[Vacation Leave], [Email Address]:[Email Address], [Email Address]2, Status:Status, "Approved"))

 

Employee Directory Range 1 refers to the number of Eligible Vacation Days referenced in my cross sheet.

Employee Directory Range 4 refers to the Email Addresses referenced in my cross sheet (I've opted to use email addresses as opposed to employee name as it works better). 

 

Also, is there a way to set Status:Status to include both "Approved" or "Declined"? 

Again, this is very helpful, much appreciated! 

Paul Newcome

What is in the [Vacation Leave] column?

 

You may also want to try wrapping the INDEX/MATCH portion in a VALUE statement.

 

VALUE(INDEX(.........., MATCH(.................)))

 

For the Approved or Declined status... Are you wanted to include them both or calculate separately? If you are trying to include them both you have two options for your criteria.

 

..........Status:Status, NOT(@cell = "Submitted"))

 

or

 

.................Status:Status, OR(@cell = "Approved", @cell = "Declined"))

ichen

Hi Paul, 

My Leave Request sheet is set up to capture different types of leaves including vacation leave, sick leave, in-lieu leave, unpaid leave, etc. 

The formula that you've been helping me with is to calculate our employees remaining vacation leaves for the year, after their request has been Approved / Declined. 

Perhaps the image below will help illustrate what I'm trying to do... 

Again, Thank you!