Excel Formula to Display Month Numbers Within a Certain Period

Question

I'm in need of some formula assistance in Excel. I'm trying to use the dates in columns G and H (period start date and period end date) to automatically fill out the matrix on the right (columns O through Z) as I have currently manually completed it. For example, if a period starts on 1/1/20 (cell G5) and ends on 6/30/20 (cell H5), I need cells O5:Z5 to show 1, 2, 3, 4, 5, 6, null, null, null, null, null, null. If the period starts on 10/1/20 and ends on 3/31/21, then the cells in O5:Z5 would show null, null, null, null, null, null, null, null, null, 10, 11, 12.

Any help is much appreciated!! Thank you!

I've tried several IF(AND and IF(OR formulas using date references. I've been able to get most of the rows to populate using the following formula, but the problem is the rows with dates that span two years (ex: row 14, which spans 2020 and 2021).

=IF(AND(MONTH(O$4)>=MONTH($G5),MONTH(O$4)<=MONTH($H5)),1,IF(AND(MONTH(O$4)>=MONTH($G5),MONTH($H5)<=12),1,""))

Please edit your question to include a data sample (as text which can be copy/pasted) along with an example of the desired output from that sample. See [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) — Ron Rosenfeld, Jun 02 '23 at 00:10
You could try this formula --> `=IF((--TEXT(O$4,"mmm-yyyy")>=--TEXT($G5,"mmm-yyyy"))*(--TEXT(O$4,"mmm-yyyy")<=--TEXT($H5,"mmm-yyyy")),MONTH(O$4),"")` this assumes the cells from `O4:Z4` are dates as well. — Mayukh Bhattacharya, Jun 02 '23 at 01:54
You can use this as well, if you have access to the `LET()` function -- `=LET( a,--TEXT(O$4,"mmm-yyyy"), b,--TEXT($G5,"mmm-yyyy"), c,--TEXT($H5,"mmm-yyyy"), IF((a>=b)*(a<=c),MONTH(O$4),""))` — Mayukh Bhattacharya, Jun 02 '23 at 01:57

Mayukh Bhattacharya · Answer 1 · 2023-06-02T04:13:02.770

As per my comments above and if i have clearly understood the requirements of your query then, posting it here, to show an example:

• Formula used in cell O5

=IF(
(--TEXT(O$4,"mmm-yyyy")>=--TEXT($G5,"mmm-yyyy"))*
(--TEXT(O$4,"mmm-yyyy")<=--TEXT($H5,"mmm-yyyy")),
MONTH(O$4),
"")

Or,

To make it more readable and to avoid repeating of formulas you can use LET() Function.

• Formula used in cell O5

=LET(
a,--TEXT(O$4,"mmm-yyyy"),
b,--TEXT($G5,"mmm-yyyy"),
c,--TEXT($H5,"mmm-yyyy"),
IF((a>=b)*(a<=c),MONTH(O$4),
""))

Notes:

• The above formulas assume that the cells from O4:Z4 are dates with the first day of each month.

• The formula --TEXT(O$4,"mmm-yyyy") converts the value in cell O$4 into a date format mmm-yyyy while the TEXT() function formats the date as a string, hence the double unary operator -- converts it back to a date serial number. Like wise it has been used for the cells in column G & H.

• Then the formulas compares if O$4 is greater than or equal to the date in $G5 and less than or equal to $H5.

• If the conditions are met i.e. if its TRUE the formula returns the month of the date in cell O$4 using the MONTH() Function, while if FALSE it returns an empty.

• However, if the dates in cells O4:Z4 are text then we can change this part

From -->

--TEXT(O$4,"mmm-yyyy")

To -->

--TEXT(DATE(2020,MONTH(O$4&1),1),"mmm-yyyy")

To make it work as per the requirement. Note that the year is hardcoded and assumed to be 2020 you use a cell reference to make it dynamic as well.

If you are using MS365 then you can use a dynamic spilled array version.

• Formula used in cell O5

=LET(
    a, G5:H6,
    b, O$4:Z$4,
    c, SEQUENCE(ROWS(a)),
    d, --TEXT(b, "mmm-yyyy"),
    e, --TEXT(
        INDEX(a, c, 1),
        "mmm-yyyy"
    ),
    f, --TEXT(
        INDEX(a, c, 2),
        "mmm-yyyy"
    ),
    IF(
        (d >= e) * (d <= f),
        MONTH(b),
        ""
    )
)

Excel Formula to Display Month Numbers Within a Certain Period

1 Answers1