代码之家 › 专栏 › 技术社区 › Davide Lorino

嵌套if语句以读取和输出自定义日期分组

excel-2016 excel-formula excel-vba vba excel

Davide Lorino · 技术社区 · 6 年前

我试图在excel 2016中创建一个嵌套的if语句来读取日期值并输出相应的财政月。由于一个财政月是我工作的公司所独有的,除了创建一个自定义函数或报表外,没有其他选择。

到目前为止,我试过:

=IF(D2 <= DATEVALUE("28/07/2017"),DATEVALUE("Jul-2017"), 
 IF(D2 <= DATEVALUE("26/08/2017"),DATEVALUE("Aug-2017"), 
    IF(D2 <= DATEVALUE("29/09/2017"), DATEVALUE("Sep-2017"), 
       IF(D2 <= DATEVALUE("27/10/2017"), DATEVALUE("Oct-2017"), 0)))) +
          IF(D2 <= DATEVALUE("24/11/2017"), DATEVALUE("Nov-2017"),
             IF(D2 <= DATEVALUE("29/12/2017"), DATEVALUE("Dec-2017"),
                IF(D2 <= DATEVALUE("26/01/2018"), DATEVALUE("Jan-2018"),
                   IF(D2 <= DATEVALUE("23/02/2018"), DATEVALUE("Feb-2018"), 0)))) +
                      IF(D2 <= DATEVALUE("20/03/2018"), DATEVALUE("Mar-2018"),
                         IF(D2 <= DATEVALUE("27/04/2018"), DATEVALUE("Apr-2018"),
                            IF(D2 <= DATEVALUE("25/05/2018"), DATEVALUE("May-2018"),
                               IF(D2 <= DATEVALUE("29/06/2018"), DATEVALUE("Jun-2018"),
                                  IF(D2 <= DATEVALUE("27/07/2018"), DATEVALUE("Jul-2018"),
                                     IF(D2 <= DATEVALUE("24/08/2018"), DATEVALUE("Aug-2018"), 0)))))) +
                                        IF(D2 <= DATEVALUE("28/09/2018"), DATEVALUE("Sep-2018"),
                                           IF(D2 <= DATEVALUE("26/10/2018"), DATEVALUE("Oct-2018"),
                                              IF(D2 <= DATEVALUE("23/11/2018"), DATEVALUE("Nov-2018"),
                                                 IF(D2 <= DATEVALUE("28/12/2018"), DATEVALUE("Dec-2018"),
                                                    IF(D2 <= DATEVALUE("25/01/2019"), DATEVALUE("Jan-2019"),
                                                       IF(D2 <= DATEVALUE("22/02/2019"), DATEVALUE("Feb-2019"),
                                                          IF(D2 <= DATEVALUE("29/03/2019"), DATEVALUE("Mar-2019"), 0))))))) +
                                                             IF(D2 <= DATEVALUE("26/04/2019"), DATEVALUE("Apr-2019"), 
                                                                IF(D2 <= DATEVALUE("24/05/2019"), DATEVALUE("May-2019"), 
                                                                   IF(D2 <= DATEVALUE("28/06/2019"), DATEVALUE("Jun-2019"), D2)))

它不工作-我得到的是数字输出而不是日期。

必须有更好/有效的方法来做这件事。

有人能推荐另一种方法吗?是否可以通过VBA中的UDF?

编辑-过帐范围和所需输出的表

Start date  End date    Month
12/30/2017  1/26/2018   Jan-18
1/27/2018   2/23/2018   Feb-18
2/24/2018   3/30/2018   Mar-18
3/31/2018   4/27/2018   Apr-18
4/28/2018   5/25/2018   May-18
5/26/2018   6/29/2018   Jun-18
6/30/2018   7/27/2018   Jul-18
7/28/2018   8/24/2018   Aug-18
8/25/2018   9/28/2018   Sep-18
9/29/2018   10/26/2018  Oct-18
10/27/2018  11/23/2018  Nov-18
11/24/2018  12/28/2018  Dec-18
12/29/2018  1/25/2019   Jan-19
1/26/2019   2/22/2019   Feb-19
2/23/2019   3/29/2019   Mar-19
3/30/2019   4/26/2019   Apr-19
4/27/2019   5/24/2019   May-19
5/25/2019   6/28/2019   Jun-19

0 回复 | 直到 6 年前

urdearboy 6 年前

表中的模式是:

每个月的最后一个星期五是截止月份,这意味着…
每个月的最后一个星期六是下个月的开始

这将接受一个输入,验证它是一个日期,并查看该日期是否在输入月份的最后一个星期五之前、等于或之后。

如果以前, CSTMonth = Inputs Month
如果相等, cstmount=输入月
如果以后, CSTMonth = Month after Inputs Month

Option Explicit

Public Function CSTMonth(Target As Range)

If IsDate(Target) Then

  Dim LastDay As Date: LastDay = WorksheetFunction.EoMonth(Target, 0)
  Dim LastFri As Date: LastFri = LastDay - (Weekday(LastDay + 1) Mod 7)

    If Target <= LastFri Then
        CSTMonth = Format(Target, "MMM-YY")
    Else
        CSTMonth = Format(LastDay + 1, "MMM-YY")
    End If

End If

End Function

在表中查找模式的好处是永远不需要用 brute force . 采用这种方法意味着要对每个月的开始和结束日期进行硬编码。

EEM 6 年前

如果使用的表中包含每个时段的开始日期,请尝试以下操作:

假设具有时段起始日期的表位于 B2:D25 日期在 F2:F25 和 J2:J25

输入此公式以获取财政期间作为日期序列 G2 然后复制到 G3:G25 和 K2:K25 :

=VLOOKUP(F3,$B$2:$D$25,3,1)

输入此公式以获取财政期间作为文本 H2 然后复制到 H3:H25 和 L2:L25 :

=TEXT(VLOOKUP(F3,$B$2:$D$25,3,1),"mmm-yyyy")

-1

shrivallabha.redij 6 年前

我不确定你所面临的复杂情况,但是为了得到你想要的结果,你也可以使用一个更简单的公式,比如

=TEXT(EOMONTH(A2,1),"YY-MMM")

或

=EOMONTH(A2,1) 将单元格格式化为 yy-mmm 格式。

哪里 A2 保留开始日期。