-1

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

2 Answers 2

6

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

enter image description here

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.

3
  • 3
    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. Jul 11, 2019 at 19:07
  • @G-man, yes! Now that's a clear thought! ;), Should I add this in?
    – JvdV
    Jul 11, 2019 at 19:22
  • Yes,­ go ahead. Jul 11, 2019 at 19:34
3

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

enter image description here


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"))
4
  • 2
    Great stuff! Just another option: =TEXT(EDATE(A2,IF(DAY(A2)<17,-2,-1)),"MMM")
    – JvdV
    Jul 11, 2019 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. Jul 11, 2019 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, 2019 at 18:22
  • 1
    Put it as an answer, someone else may get needed help later Jul 11, 2019 at 18:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .