Auto monthly due date update

Question

I need help understanding this formula that I found online:

=DATE(YEAR(A2),IF(DAY(A2)\<=15,MONTH(A2),MONTH(A2)+1),15)

Assumption made:

1. Bill date (from the 1st day of the month to the 15th day of the month) - Due date falls on the 15th day of the same month

2. Bill date (from the 16th day of the month to month end) - Due date falls on the 15th day of the following month

3. Assume A2 cell contains the Bill date. "

Online example for formula: "If my bill is due November 15, 2023, when the date is November 16, 2023, I want the cell to auto-change to December 15, 2023, and every month after that."

If I can understand this formula better, I want to apply this formula using this Excel template to track recurring (monthly and annual) charges to my credit card:

https://create.microsoft.com/en-us/template/subscription-and-membership-tracker-b86f671c-8d23-4af3-84ee-5d1d9fd9b07f

Here's a preview of this template that I customized using this formula:

This is my data using this template:

I created a 2nd sheet copying the same column data from sheet 1 (columns B and C from sheet 1) to test the formula, but the result in column C (sheet 2) shows duplicate dates. Some of these dates should show the following billing month already, correct? What am I doing wrong? Here is a screenshot of sheet 2 using the formula and an additional screenshot of sheet 2 showing the formulas for each cell:

I would appreciate any help getting these dates correct. Thank you, all!

Answer 1

To understand the formula....

The DATE formula accepts three numbers for the year, day and month. So todays date would be =DATE(2023,11,22).

• YEAR(A2) will take the year from the date in cell A2 and apply it to the first argument in the DATE formula.
• IF(DAY(A2)<=15,MONTH(A2),MONTH(A2)+1) looks at the day number in cell A2. If it's <= 15 it will use the month from the date, otherwise it will add 1 to the month.
• 15 is a static value indicating the day.

---

• So, if your date in A2 is 14-Dec-2023 it will return 15-Dec-2023 as it doesn't add 1 to the month.
• If your date is 16-Dec-2023 it will return 15-Jan-2024 as it has added 1 to the month (i.e. it calculates to DATE(2023,13,15) and the 13th month of 2023 is January 2024.

So looking at your last image - row 2 will show the 10th day, row 3 will show the 7th day, row 4 the 13th, and so on. These will all need changing to 15 if your Bill date is the 15th.