dates overlap dynamic

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

Regards,
Ankur

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.

I think you will need a VBA solution for this.

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

Well beyond my capabilities!

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

Function

=LEAVES(B3,C3)

Option Explicit
Function Leaves(StDate As Date, FnDate As Date) As Integer


Dim inArr() As Variant

Dim r As Long
Dim c As Long
Dim j As Long
Dim i As Long

Dim sr As Long
Dim fr As Long
Dim lSum As Integer
Dim n As Integer
Dim lastrow As Long
Dim lastcol As Long
Dim Start_date As Date

Dim inRng As Range

Dim ws1 As Worksheet

Dim lDates(1 To 740)
'
'  Array with values of Leave dates (1=Leave Day)
'
For i = 1 To 740
    lDates(i) = 0
Next i

Start_date = DateValue("January 01, 2015")

Set ws1 = Worksheets("Sheet1")


ws1.Activate

With ws1
    
    lastrow = .Cells(Rows.Count, 5).End(xlUp).Row
    lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
    
    Set inRng = .Range(.Cells(3, 5), .Cells(lastrow, 6))
    
    inArr = inRng
    
   '
   ' Set "LDates" to 1 if date is non-weekend  Leave date
   '
   
   For r = 1 To UBound(inArr, 1)
   
        sr = inArr(r, 1)
        fr = inArr(r, 2)
        
        For j = sr To fr
          n = Application.WorksheetFunction.Weekday(j, 2)
          If n <= 5 Then
            i = j - Start_date + 1
            lDates(i) = 1
            End If
        Next j
        
   Next r
    
        lSum = 0
        sr = StDate - Start_date + 1
        fr = FnDate - Start_date + 1
            For i = sr To fr
                lSum = lSum + lDates(i)
            Next i
    
    
End With

Leaves = lSum

End Function

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.

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.

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

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

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))

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.

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))

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.

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!

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

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).