Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try this expression:
=count({<StartDate={">=$(VacStartDate)<=$(VacEndDate)"}>+
<EndDate={">=$(VacStartDate)<=$(VacEndDate)"}>+
<StartDate={"<$(VacStartDate)"},EndDate={">$(VacEndDate)"}>} distinct EmployeeId)
Try this expression:
=count({<StartDate={">=$(VacStartDate)<=$(VacEndDate)"}>+
<EndDate={">=$(VacStartDate)<=$(VacEndDate)"}>+
<StartDate={"<$(VacStartDate)"},EndDate={">$(VacEndDate)"}>} distinct EmployeeId)
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.