[Solved] Need assistance with Auto-generating billing cycle dates

[GTH]

Hello all, hoping someone can help!

I use a spreadsheet to track credit card charge/balances. Each credit card has it's own tab that references data from a master tab in the workbook where I enter new info (balances, billing cycles, minimum payments, etc.). On each individual credit card tab I have 2 columns for Billing Period with "start date" in one cell & "end date" in the next. I want those dates to change automatically at the start of the designated billing period each month so that the first rows always return the current billing period.

My problem is that the formula I'm using changes on the 1st of each month, not at the actual start of the designated billing period referenced from the master tab.

For example:
Credit Card #1 billing period = 27th to 26th of each month
Today's date is 2/13/25
The formula is returning: 2/27/25 & 3/26/25 (correct duration, but wrong dates)
I would like it to return: 1/27/25 & 2/26/25 until we reach 2/27/25 and then automatically update to 2/27/25 & 3/26/25

The formula I'm using is =DATE(YEAR(TODAY()) ,MONTH(TODAY()) ,Summary!I10

Note that Summary!I10 is a reference to the master tab where the credit card's Billing Period Start Date (27 in this case) is located.

Any help is much appreciated!

 

[Shamima Sultana]

Hello GTH,
Your current formula is always setting the billing period to the current month based on today's date, rather than aligning it with the actual billing cycle. Try adjusting your formula like this:

Formula:
=IF(DAY(TODAY())>=Summary!I10, DATE(YEAR(TODAY()), MONTH(TODAY()), Summary!I10), DATE(YEAR(TODAY()), MONTH(TODAY())-1, Summary!I10))

• If today's day is greater than or equal to the billing start date (e.g., 27), it sets the start date to this month's 27th.
• Otherwise, it sets the start date to last month's 27th.
• For the end date, just add 1 month to the start date using:

Formula:
=EDATE(Your_Start_Date_Cell,1)-1
This should ensure the billing period is updated correctly at the start of each cycle.

 

[GTH]

Thank you! I knew I needed an If/Then here somewhere but couldn't figure out the order. This worked exactly as I needed.

 

[shamimarita]

You are most welcome. Glad to hear that our solution worked. Keep exploring Excel with ExcelDemy. Let's help each other make the community stronger.