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>
[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Start date[/TH]
[TH]Count Date[/TH]
[TH]Expected result
[/TH]
[/TR]
[TR]
[TD]22/04/2019[/TD]
[TD]22[/TD]
[TD]21/05/2019[/TD]
[/TR]
</tbody>[/TABLE]


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

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]

Start date​

[/td][td]

Count Date​

[/td][td]

Expected result​

[/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]

22/04/2019​

[/td][td]

=NETWORKDAYS(A2,C2,E4)​

[/td][td]

21/05/2019​

[/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td][/td][td][/td][td][/td][td]annual day[/td][td]

15/05/2019​

[/td][/tr]

[tr][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[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