Retutn only Buisness days in custom date function in excel

Question

I have a date which I will need to increment the date by a day, month or year from the actual date. This is based of a list of fields below and is different per the input. Also each resultant date has to be a business day i.e not a weekend day or a bank holiday. Also if the resultant date is either Dec 25 or Jan 1st the day needs to move forward to the next business day (not a weekend day).

I have created this in Excel using a couple of formulas though it is a bit clunky.

Below is my data set

Business Date 15/05/2018

Tenor   Settlement Value    Settlement Period
ON  1   Day
TN  2   Day
SP  2   Day
SN  3   Day
1W  7   Day
2W  14  Day
3W  21  Day
1M  1   Month
2M  2   Month
3M  3   Month

In column E - I am using formula

=IF(D4="Day",$B$1+C4,IF(D4="Month",EDATE($B$1,C4),(TEXT($B$1,"dd/mm/")&(YEAR($B$1)+C4))+0))

In column F - I am using formula

=E4+LOOKUP(WEEKDAY(E4),{1,2,3,4,5,6,7},{1,0,0,0,0,0,2})

In column G - I am using formula

=F4+IF(AND(OR(TEXT(F4,"ddmm")="2512",TEXT(F4,"ddmm")="0101"),WEEKDAY(F4)>=2,WEEKDAY(F4)<=6),LOOKUP(WEEKDAY(F4),{1,2,3,4,5,6,7},{0,1,1,1,1,3,0}),LOOKUP(WEEKDAY(F4),{1,2,3,4,5,6,7},{1,0,0,0,0,0,2}))

In H I format the date in mm/dd/yyyy and I have my desired result.

storax has kindly created a function for me which replicates my excel formula in column E - on this thread Increment a date by a number of days, months or years

Function IncDate(ByVal dt As Date, ByVal add As Long, ByVal dmy As String) As Date
    Select Case UCase(dmy)
        Case "DAY"
            IncDate = DateAdd("d", add, dt)
        Case "MONTH"
                IncDate = DateAdd("m", add, dt)
        Case "YEAR"
                IncDate = DateAdd("yyyy", add, dt)
        Case Else
                IncDate = dt
    End Select

Could use some advise on how I could incorporate my formulas in columns F & G to make the process less clunky.

research workday.intl and networkdays.intl at support.office.com. — user4039065

Unknown Unknown · Accepted Answer · 2018-06-01T09:46:29

Manipulating the DATE function (DateSerial in vba) with the WORKDAY.INTL function seems to produce the correct business dates.

Put this in E4 and fill down.

=WORKDAY.INTL(DATE(YEAR(B$1)+(D4="year")*C4, MONTH(B$1)+(D4="month")*C4, DAY(B$1)+(D4="day")*C4)-1, 1, 1, holidays)

[holidays] is a named range (Formulas, Defined Names, Defined Name) with a Refers To: of,

=Sheet10!$Z$2:INDEX(Sheet10!$Z:$Z, MATCH(1E+99, Sheet10!$Z:$Z))

Retutn only Buisness days in custom date function in excel

1 Answers