# Excel: Calculate MONTH based on DAY

I have a list of dates. I would like a column to calculate a MONTH based on the day of the month.

If the day is 17 or greater, then show the previous month. If the day is less than 17, then show the month that is 2 prior.

``````**DATE       MONTH NEEDED**
6/19/2019        MAY
6/12/2019        APRIL
6/5/2019         APRIL
5/29/2019        APRIL
5/22/2019        APRIL
5/15/2019        MARCH
5/8/2019         MARCH
5/1/2019         MARCH
4/24/2019        MARCH
``````

I puzzled together another answer, making use of `EDATE`, a rather useful tool in this specific case: Formula in `B2`:

``````=TEXT(EDATE(A2,IF(DAY(A2)<17,-2,-1)),"MMMM")
``````

Or, as per @ScottCraner’s comment:

``````=TEXT(EDATE(A2,-1-(DAY(A2)<17)),"MMMM")
``````

Or even shorter, as per @G-Man’s comment:

``````=TEXT(EDATE(A2-16,-1),"MMMM")
``````

If the day of `A2` is 17 or greater, then `A2-16` is earlier in the same month.  If the day of `A2` is less than 17, then `A2-16` is in the previous month.

Drag down.

• Or even simpler, `=TEXT(EDATE(A2-16,-1), "MMMM")`. If the day of `A2` is 17 or greater, then `A2-16` is in the same month.  If the day of `A2` is less than 17, then `A2-16` is in the previous month. – G-Man Jul 11 at 19:07
• @G-man, yes! Now that's a clear thought! ;), Should I add this in? – JvdV Jul 11 at 19:22
• Yes,­ go ahead. – G-Man Jul 11 at 19:34

This will subtract 1 month then another if the day of the month is less than 17:

``````=TEXT(DATE(YEAR(A2),MONTH(A2)-1-(DAY(A2)<17),DAY(A2)),"MMMM")
`````` If you want the month name in all upper case then wrap in `UPPER`:

``````=UPPER(TEXT(DATE(YEAR(A2),MONTH(A2)-1-(DAY(A2)<17),DAY(A2)),"MMMM"))
``````
• Great stuff! Just another option: `=TEXT(EDATE(A2,IF(DAY(A2)<17,-2,-1)),"MMM")` – JvdV Jul 11 at 18:18
• @JvdV: `=TEXT(EDATE(A2,-1-(DAY(A2)<17)),"MMMM")` Is probably the most efficient method. I forget about `EDATE` as I do not use it much. – Scott Craner Jul 11 at 18:20
• Me neither, in fact I just puzzled it together, seeing if there was another way. Like a hobby :) ohwell – JvdV Jul 11 at 18:22
• Put it as an answer, someone else may get needed help later – Scott Craner Jul 11 at 18:22