Counting Date Excluded Weekend

[muhammad susanto]

hi all..

how to make formula to get date excluded weekend/holidays
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>

Start date	Count Date	Expected result
22/04/2019	22	21/05/2019

<tbody>
</tbody>

thanks in advance..

sst.

 

[Michael M]

You can use this but you will need to have a list of holiday dates as the 3rd variable in the formula

Row\Col	A​	B​	C​	D​	E​
1​	Start date​	Count Date​	Expected result​
2​	22/04/2019​	=NETWORKDAYS(A2,C2,E4)​	21/05/2019​
3​
4​				annual day	15/05/2019​
5​
6​

 

[muhammad susanto]

hi M...sorry i mean in count date (col.B) is data given not use formula

 

[JoeMo]

Lightly tested but give this UDF a try. Change the holidays list to suit. Note I'm using US date system.

Excel Workbook
	A	B	C	D	E
1	Start Date	Count Date	Result		Holidays
2	4/22/2019	22	5/22/2019		4/29/2019
3					1/1/2019
Sheet1

Function EndDate(StartDate As Date, NetDays As Long, HolDays As Range) As Date
Dim ct As Long, NxtDate As Date
NxtDate = StartDate
Do
    For i = 1 To HolDays.Count
        If NxtDate = HolDays(i) Then GoTo Nx
    Next i
    If Weekday(NxtDate) <> vbSunday And Weekday(NxtDate) <> vbSaturday Then
        ct = ct + 1
        If ct = NetDays Then Exit Do
    End If
Nx:    NxtDate = NxtDate + 1
Loop
EndDate = NxtDate
End Function

 

[muhammad susanto]

hi Joe....

thanks you s0 much

 

[JoeMo]

hi Joe....

thanks you s0 much

You are welcome - thanks for the reply. Here's a version of the UDF that eliminates looping through the Holiday dates. Should be a bit faster in case you employ the UDF in many cells throughout a worksheet.

Function EndDate(StartDate As Date, NetDays As Long, HolDays As Range) As Date
Dim ct As Long, NxtDate As Date, IsHoliday As Variant
NxtDate = StartDate
Do
    IsHoliday = Application.Match(CLng(NxtDate), HolDays, 0)
    If Not IsError(IsHoliday) Then GoTo Nx
    If Weekday(NxtDate) <> vbSunday And Weekday(NxtDate) <> vbSaturday Then
        ct = ct + 1
        If ct = NetDays Then Exit Do
    End If
Nx:    NxtDate = NxtDate + 1
Loop
EndDate = NxtDate
End Function