-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
5

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.

  • 2
    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
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"))
  • 2
    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
  • 1
    Put it as an answer, someone else may get needed help later – Scott Craner Jul 11 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.