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

Options

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. 

Tags:

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!