dates overlap dynamic

[shishm]

I need to calculate leaves (X, Y ) during a specified task by an employee.


Task Workdays
Start (Column D ) << >> End (Column E ) ------ Leaves days (column F )
1 10/10/2015 <<>> 11/15/2015 ---- X
2 11/27/2015 <<>> 12/27/2015 ---- Y
3. task3 (Start dt. <<>> End Dt. ) ------ Z
4. task4
5..... 2/5/2016 2/5/2015....


The leaves /Period :
Leaves
Column G << >> Column H
Start <<>> End
10/4/2015 <<>> 10/10/2015
11/10/2015 <<>>11/13/2015
12/2/2015 <<>> 12/8/2015
2/24/2015<<>> 1/1/2016
2/5/2016 <<>> 2/5/2015
and so on.......

The task list will be ever increasing, and so will be the leaves .

i need to find leaves taken during the the task 1 (X ) , similarly for task 2 (Y )
the leaves table will be say 200 rows to start with and gradually scalable, same with task list
also need to account workdays only (remove week ends )

[shukla.ankur281190]

What would be expected result ??? Can you make some sense clear once??

Regards,
Ankur

[shishm]

if you open the excel you will have clear idea, Expected result X - 4 days, Y - 7 days, exclude weekends only work days to be considered.

[JohnTopley]

I think you will need a VBA solution for this.

[shishm]

I believe that it can be done using MMULT , SUM , Index, ROW in array formula, however i am not familiar in formulating one myself

[JohnTopley]

Well beyond my capabilities!

[JohnTopley]

See attached: VBA will need to be adjusted to your worksheet data format.

Function

=LEAVES(B3,C3)

Please Login or Register  to view this content.

[shishm]

I opened your sheet, the sheet works when i change the dates on the task range, i get the desired results, however when i changed the leaves dates range it did not change at all. also both date ranges are pulled from different sources to this one sheet. and as of now there are 300 rows for task, which will be increased as per new task
Also this sheet will be used both on excel and also google spreadsheet.

[JohnTopley]

I cannot offer a formulaic solution so you will have to hope someone can provide one.

I don't understand the comment about the "Leaves" dates: all the routine does is use these dates to determine valid leave data. It is the task dates that cause changes using the function.

The range of 300 rows is immaterial as the macro automatically extends the "Task Dates" it processes. You only have to drag the formula down.

[shishm]

theres one post who has done this, trying to figure out how to do it for workdays, he has not done it for workdays

http://www.get-digital-help.com/2015...e-date-ranges/

also it gives different result in google spreadsheet

[JohnTopley]

I have looked at the formula but I have no idea how to "convert" it to workdays.

[JohnTopley]

See attached sheet2

Formula is ..

=SUMPRODUCT((IFERROR((MMULT(TRANSPOSE($E$2:$E$6^0),(TRANSPOSE($A2+ROW($A$1:INDEX($A:$A,$B2-$A2+1))-1)>=$D$2:$D$6)*(TRANSPOSE($A2+ROW($A$1:INDEX($A:$A,$B2-$A2+1))-1)<=$E$2:$E$6)))^0,0))*(WEEKDAY(TRANSPOSE($A2+ROW($A$1:INDEX($A:$A,$B2-$A2+1))-1),2)<6))

[shishm]

Yes John the formula gives correct result, what you did is excellent , still two issues to it, 1.) as i said date range in column - D and E, can go up to 300 rows, when try to increase this to cover 300 rows, it shows 0 value., as of now the out of this 600 rows much is blank and will be filled gradually 2.) also i need the same to work on google spreadsheet too.

[JohnTopley]

This formula uses dynamic named ranges for "Leave Start" and "Leave End". As I said before the entries in D and E have no direct bearing on the results which are dictated by what is in A & B. I extended entries in D & E to over 300 and everything was OK.

for "Leave_End" named range ...: "Leave_Start" change E to D

=OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E)-1,1)

Post a file showing errors.

I nothing about Google spreadsheets so cannot help on this.

=SUMPRODUCT((IFERROR((MMULT(TRANSPOSE(Leave_End^0),(TRANSPOSE($A2+ROW($A$1:INDEX($A:$A,$B2-$A2+1))-1)>=Leave_Start)*(TRANSPOSE($A2+ROW($A$1:INDEX($A:$A,$B2-$A2+1))-1)<=Leave_End)))^0,0))*(WEEKDAY(TRANSPOSE($A2+ROW($A$1:INDEX($A:$A,$B2-$A2+1))-1),2)<6))

[shishm]

thanks John, i did create 300 rows and checked, i need to fill all the 300 rows in column - D and E for the formula to work, so i made a dummy date range (earlier dates ) to check, so results correct, however my dates in column D & E , will only be there as and when they are punched, normally the cells will be blank. whereas the formulas in row C will be filled for all the 300 rows so that calculation is dynamic. any suggestions apart from filling dummy dates. if not then i will have to work around with dummy dates.

[JohnTopley]

I am confused about which dates are which: assuming task dates are A & B ,C is calculation , D & E are Leave dates then in C

=IF(A2="","",SUMPRODUCT((IFERROR((MMULT(TRANSPOSE(Leave_End^0),(TRANSPOSE($A2+ROW($A$1:INDEX($A:$A,$B2-$A2+1))-1)>=Leave_Start)*(TRANSPOSE($A2+ROW($A$1:INDEX($A:$A,$B2-$A2+1))-1)<=Leave_End)))^0,0))*(WEEKDAY(TRANSPOSE($A2+ROW($A$1:INDEX($A:$A,$B2-$A2+1))-1),2)<6)))

Enter with Ctrl+Shift+Enter

Simply really!

[shishm]

thanks John, couldn't have been possible without you, will save lots of effort for me, thanks

[JohnTopley]

Thank you for feedback.

I learned a lot myself trying to find a solution (with your help pointing to the Forum with the basis for the final solution).