将日期增加几天，几个月或几年 [英] Increment a date by a number of days, months or years

问题描述

我有一个日期，需要从实际日期起将日期增加一天，一个月或一年.这基于下面的字段列表，并且每个输入都不同.同样，每个结果日期都必须是工作日，即不是周末或银行假日.我现在将放弃银行假期的要求.

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. I will forgo the Bank Holiday requirement now.

我正在使用公式 = DATE(YEAR($ A $ 1)，MONTH($ A $ 1)，DAY($ A $ 1))并根据以下内容对每行中的增量值进行硬编码下方B4中的值.同样，一旦我们通过A4列中的SP，公式就会更改为引用使用SP值中的值创建的日期.

I am using the formula =DATE(YEAR($A$1),MONTH($A$1),DAY($A$1)) and hard coding the increment value in each row based of the value in B4 below. Also once we get past SP in column A4 the formula changes to reference the date created by using the value at the SP value.

因此，如果我们将结果放入E列，则公式将更改为 = DATE(YEAR($ E $ 6)，MONTH($ E $ 6)，DAY($ E $ 6))日期还必须采用 text(xx，"mm/dd/yyyy")

So if we put the results in to column E the formula will change to =DATE(YEAR($E$6),MONTH($E$6),DAY($E$6)) The date also needs to be in the format text(xx,"mm/dd/yyyy")

任何有关如何在VIA的Excel中创建VBA解决方案宏的建议

Any suggestions on how to create this in Excel of VIA a VBA solution macro would be appreciated

A1
=today()

A4 B4
ON  1 Day
TN  2 Day
SP  2 Day
SN  2 Day
1W  7 Day
2W  14 Day
3W  21 Day
1M  1 Month
2M  2 Month
3M  3 Month
4M  4 Month
5M  5 Month
6M  6 Month
7M  7 Month
8M  8 Month
9M  9 Month
10M 10 Month
11M 11 Month
1Y  1 Year
15M 15 Month
18M 18 Month
21M 21 Month
2Y  2 Year
3Y  3 Year
4Y  4 Year
5Y  5 Year
6Y  6 Year
7Y  7 Year
8Y  8 Year
9Y  9 Year
10Y 10 Year
15Y 15 Year
20Y 20 Year
25Y 25 Year
30Y 30 Year

推荐答案

我会创建一个用户定义的函数，类似这样的东西

I would create a user defined function, somthing like that

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
End Function

然后您可以用D书写

=IncDate(TODAY();B4;C4)

然后，您需要根据需要格式化单元格.

You then need to format the cell to your needs.

结果看起来像这样

这篇关于将日期增加几天，几个月或几年的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！