We help IT Professionals succeed at work.

Adding holiday requests and displaying number of days used/left in a different list

acuit asked

I have a sharepoint list that has our employee details with several columns, one of whihc is "Number of days Holiday" = 25.

In a seperate list with Calendar view, users create Holiday requests which (data filled in is start date, end date and number of days used) are then sent via the Approval workflow, the calendar is filtered to only show apporved holidays.

I would like to show in the first "Employee data" list the number of holidays used and then in a seperate column show number of days left.

i.e. Number of days holiday = 25 (employee list)  -> Days Used = 1 (calendar list) -> Days left (calculated in the employyes list) = 24

This is simpole to do in Excel but in Sharepoint i cannot for the life of me see how to calculate between 2 lists easily.

Watch Question

Ivan PadabedDirector, Technology Solutions

One of hte ways to implement your requirements with SPD only:
1. create new column on Employee list to hold Holidays Used Count
2. Update it with simple workflow after new holiday get approved
3. Create new calculated column on Employee list and display data by simple formula as in Excel
Ivan PadabedDirector, Technology Solutions

PS you can make Holidays Used column hidden if needed, or just remove it from user Views


What would happen if the employee decided not to take holiday and we deleted the holiday from the Calendar list?

This happened a few times last year.

I need to Sum all the approved holidays in the calendar list and take that figure and isert it into the employees list.

So no matter whats happens i have upto date figures.
Ivan PadabedDirector, Technology Solutions

well, you can create one more workflow and run it on ItemDelete to recalculate Holidays Used Count back :) and you don't need to Sum all everytime, workflow will keep the values in sync for you by adding/removing Holidays count right after the Holiday item added/removed


hi thanks for the info.

I have not used SPD before to Add a number to a list, any suggestions to help me?
Ivan PadabedDirector, Technology Solutions

there is pretty good step by step instruction on exactly your case (with a little bit different approach)