CODEWITHSUNDEEP
excelvbaexcel-formulaexcel-2016
Davide Lorino
Davide Lorino

Reputation: 915

Nested IF statement to read and output custom date groupings

I am trying to create a nested IF statement in Excel 2016 to read a date value and output the corresponding financial month. Since a financial month is unique to the company I work for there is no available alternative other than to create a custom function or statement.

So far I have tried:

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

It isn't working - I get a numeric output instead of a date.

There must be a better/effective way of doing this.

Can somebody recommend another approach? Possible via UDF in VBA?

Edit - posting a table of the ranges and required output

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

Upvotes: 1

Views: 59

Answers (3)

EEM
EEM

Reputation: 6659

If you are using a table with the starting dates for each period the try the following:

Assuming the table with the period starting dates is located at B2:D25 and the dates are located at F2:F25 and J2:J25

Enter this formula to obtain the Financial Period as date serial in G2 then copy it to G3:G25 and K2:K25:

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

Enter this formula to obtain the Financial Period as text in H2 then copy it to H3:H25 and L2:L25:

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

enter image description here

Upvotes: 1

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

I am not sure about the complications you are facing but to get the desired results per your table, you can also use a simpler formula like

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

OR

=EOMONTH(A2,1) and format the cell in yy-mmm format.

where A2 holds the start date.

Upvotes: -1

urdearboy
urdearboy

Reputation: 14580

The pattern in your table is:

  1. The last Friday in each month is the month cut off, which means....
  2. The last Saturday in each month is the start of the next month

This will take a input, verify that it is a date, and see if the date is before, equal to, or after the last Friday in the input month.

  1. If before, CSTMonth = Inputs Month
  2. If equal, CSTMonth = Inputs Month
  3. If after, 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

The benefit of finding the pattern in your table is that this will never need to be updated with brute force. Taking your approach would mean hard coding each and every months start and end dates.

Upvotes: 1

Related Questions