Need to Figure out # of days each emplyee is on leave in a given month

Gajapathi Muniyappa

Hi Everyone,

I'm working on a work days calculation for a given month, for that I need to calculate the leave days for a given month. Below is my grid.

1. Orage colors columns is in different sheet - Leave requests are updated here and green color columns is in different sheet - Need to figure out total # of days on leave in a given month per employee.
2. I need to exclude week ends
3. Return date is actual date were emplyee return from leave, hence I need to exclude that day
4. Expected results already updated under April and May month

Can any one help me out from this?

@Paul Newcome @Andrée Starå @Genevieve P. 

Gajapathi Muniyappa

Any luck here @Paul Newcome @Andrée Starå @Genevieve P. 

Genevieve P.

Hi @Gajapathi Muniyappa

You could add a helper column on your main source sheet the returns the working days per row as an automatic calculation.

Assuming the end date will always be a working day, you could do a very simple calculation of:

=NETWORKDAYS([Start Date]@row, [Return Date]@row) - 1

Then in your second sheet you can use SUMIFS to SUM the value in your helper column by the name:

=SUMIFS({Helper Column with numbers}, {Name column}, [Name Column]@row, {Start Date Column}, IFERROR(MONTH(@cell), 0) = 4)

However this won't work if you have a row where the Start Date is in one month but the End Date is in another. ( e.g. Wed 31-May to Fri 02-Jun).

Is there a reason you need the calculations to be in a second sheet? What you could do is have all of your Month columns in this current sheet so we can do a row evaluation that way. Then you could use a Report to Group and Summarize based on specific filters.

Thanks!
Genevieve