7

I am trying to sequential dates over a period of months based on the given input date.

enter image description here

Current excel formula: =--(MONTH(B2+32) & "/" & DAY(B$2) & "/" & YEAR(B2+32))

Input date format: DD-MM-YYYY

Version: Microsoft office Home and Student 2019

Can someone please provide the excel formula for this?

4
  • 2
    There are issues with all of the solutions if the initial date is on the 28th or later. eg initial date 30-Sep-2022. Do you want subsequent dates to be on the 30th? or on the last day of the month? What about 30-Jan-2022. If your dates will always be prior to the 28th, then there should not be an issue, but if not, think carefully about what you want. May 30, 2022 at 11:30
  • @RonRosenfeld Consider this every month installment date. For the 28th or later dates, it should be the same day or best possible max day of next month. May 31, 2022 at 18:57
  • Then I suggest to use an IF to determine whether to use an EDATE function or, possibly the EOMONTH function May 31, 2022 at 21:01
  • @RonRosenfeld Sounds good! Can you provide it as answer? Jun 1, 2022 at 5:24

2 Answers 2

5

Use the EDATE function:

Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue.

=EDATE(B2,1)

0
5

With the start date in A1
Type this in A2:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
(fill down)

Expected to work in all Excel, Libreoffice and OpenOffice versions.

1
  • Note; the result "here" will be a valid date; therefore you can use the normal cell formatting to DISPLAY it in any valid date format - i.e. no need to build it up with concatenated strings.
    – Hannu
    Jun 7, 2022 at 16:20

You must log in to answer this question.

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