Number of employees on Vacation For A Date Period

[#1]

Dear Community.

I want to count  the number of employees on vacation for a given date range.

My table contains EmployeeID ,vacationStartDate and EndDate.

I used two inputboxes so that user can select any date ranges from input boxes.

when a user inputs  a startdate 2013/01/04 and an Enddate 2013/01/05. then i need to see how many employees on vacation during these period date ranges including those employees who took vacaction before this period and have not returned yet.

Suppose an employeeId 3 is has taken on vacation from 2012/12/1 to 2013/2/10.

now from input box user has entered the startdate 2013/01/02 and end date 2013/01/10 but since the employee with ID 3 is still on vacation, his count should also be included for the date range entered by user.

Please find the sample file attached.

Thanks,

Mukram.

[Gysbert_Wassenaar · Accepted solution]

Try this expression:

=count({<StartDate={">=$(VacStartDate)<=$(VacEndDate)"}>+

            <EndDate={">=$(VacStartDate)<=$(VacEndDate)"}>+

            <StartDate={"<$(VacStartDate)"},EndDate={">$(VacEndDate)"}>} distinct EmployeeId)

[#3]

Dear Gysbert Wassenaar,

I highly appreciate your help regarding the issue.

it's working like a charm.

once again Thank you so much for your help.

Thanks,

Mukram.