Share via

Auto-fill dates in Excel with 15th and End-of month only.

Anonymous
2020-11-17T17:22:36+00:00

Hi.  I want to auto-fill dates down a column and have it populate with the 15th and ending day of the month only:

15-JAN-21,

31-JAN-21,

15-FEB-21,

28-FEB-21,

15-MAR-21,

31-MAR-21,

15-APR-21,

30-APR-21

When I attempt this, I get a fixed increment of 15 days which is not what I want occurring.  Is this even possible?

Thanks!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2020-11-17T17:50:37+00:00

    Hi TT30

    Please try   =IF(DAY(A1)<16,EOMONTH(A1,0),EOMONTH(A1,0)+15)

    I hope this helps you and gives a solution to your problem

    Regards

    Jeovany

    30+ people found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2020-11-17T18:07:23+00:00

    Hi TT30. I’m RN, an Independent Advisor and Microsoft user like you.

    I'm sorry to hear about this issue.

    Auto fill functions cannot handle the expected result you want. You will be needing a formula to achieve this one.

    First in "A1" cell, input the "15-JAN-21".

    Then please use formula below in "A2", afterwards drag this formula to the next row to compute for the next month and day.

    =IF(DAY($A1)<=15,EOMONTH($A1,0),DATE(YEAR($A1),MONTH($A1+1),15))

    10+ people found this answer helpful.
    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-11-17T18:34:00+00:00

    TT30,

    This perhaps:

    =IF(DAY(A1)<16,MIN(A1+15,EOMONTH(A1,0)),EOMONTH(A1,0)+15)

    For Feb it will give the last day, so 28 or 29.

    Jan

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2020-11-17T19:00:08+00:00

    Hi there Jan,  Thanks so much for your reply.  inputting your formula results in 30-JAN-21 in A2, but when copied down, produces 30-APR-22 in cell A3, the following line....

    0 comments
  3. Anonymous
    2020-11-17T18:13:58+00:00

    WOW!  Are you ever clever my friend!   Thanks!!

    Challenge #2:  is there a formula for the 15th and 30th of the month?  I respect that Feb 28th and Feb 29th present a problem....  

    THANKS

    0 comments