Need Help Calculating Recurring Billing Date (First_Date vs. Today)

[armstrong.evant]

Hey guys,

I need some help with a formula to calculate dates within Excel. I'm running Excel 2016 currently. I'm trying to come up with something to return a "Next Billing Date" based on a customer's subscription date versus today's date.

For example:

A customer subscribes on January 1, 2018. They are billed on a reoccurring schedule on the same DATE every month (February 1st, March 1st, April 1st, etc.). Assuming today's date is June 6th, 2018; how do I tell Excel to display the next recurring billing date (in this case, the output should be July 1st, 2018)?

I know Excel has pretty advanced date functions, and I know it's possible to generate a list of recurring dates, but I only want the spreadsheet to display the most recent relevant value.

Has anybody encountered this before, or have any advice on what functions to research in order to accomplish this?

[FDibbins]

Hi, welcome to the forum

To get exactly 1 month ahead from a specified date, use =EDATE(cell-ref,1)

[armstrong.evant]

Thanks FDibbins;

I've played with the EDATE Function and it doesn't seem to suit my purposes well enough unless it is dependent on another variable. I am trying to calculate this conditionally based on today's date.

For instance, If a customer's start date was on January 1st, 2018, and today's date is June 6th, 2018, their next billing date would be July 1st, 2018.

It might also be worth noting that another data point being tracked is the number of months successfully billed.

Please Login or Register  to view this content.

I have debated using this number as part of the equation to determine the next billing date, and an example would be as follows:

Please Login or Register  to view this content.

Can you foresee any problems with this solution?

[FDibbins]

I was thinking that you would use EDATE based in the 1st billing date. The "months" could be based on a count of the dates billed, pretty much what you have in your 2nd formula...
=EDATE(1st-billing-date, count-of-past-billing-dates)