在Excel中的自定义日期功能中仅保留商务日 [英] Retutn only Buisness days in custom date function in excel

问题描述

我有一个日期，需要从实际日期起将日期增加一天，一个月或一年。这基于下面的字段列表，并且每个输入都不同。同样，每个结果日期都必须是工作日，即不是周末或银行假日。同样，如果结果日期是12月25日或1月1日，则需要将该日期移至下一个工作日（而不是周末）。

我在Excel使用了两个公式，尽管有点笨拙。

下面是我的数据集

营业日期15/05/2018

 主要结算值结算期
 1天$ ​​b $ b TN 2天
 SP 2天
 SN 3天
 1W 7天
 2W 14天
 3W 21天
 1M 1个月
 2M 2个月
 3M 3个月
  

在E列中-我使用的是公式

  = IF（D4 = Day，$ B $ 1 + C4，IF（D4 = Month，EDATE（$ B $ 1，C4），（TEXT（$ B $ 1， dd / mm /）& （YEAR（$ B $ 1）+ C4））+ 0））
  

在F列中-我是使用公式

  = E4 + LOOKUP（WEEKDAY（E4），{1,2,3,4,5,6,7 }，{1,0,0,0,0,0,2}）
  

在G列中-我正在使用公式

= 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}））

HI格式以mm / dd / yyyy为单位的日期，我得到了想要的结果。

storax为我创建了一个函数，该函数复制E列中的excel公式-在此线程上

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.

解决方案

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

这篇关于在Excel中的自定义日期功能中仅保留商务日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！