How to calculate Next Bill Date
Requirement: Calculate the Next Bill Date for an Account, given that the Bill Cycle Day indicates the day of the month which the customer gets billed.
If the Bill Cycle Day is a number that is larger than the number of days in the given month, e.g. Bill Cycle Day = 31 and month = November, then set the Next Bill Date to the last day of the month.
Take into consideration leap years for the month of February.
Notes: To effectively calculate the formula, I had to use a reference value to ensure I calculate the dates correctly. As such, I used the month as my source of reference. This means that each IF statement used looks at the month to derive the appropriate formula. I broke down the steps that I used to calculate the final formula.
Identify if the Next Bill Date is in the current month or following month: IF(DAY(Today())>Bill_Cycle_Day, DATE(YEAR(TODAY()),MONTH(TODAY()),Bill_Cycle_Day), DATE(YEAR(TODAY()), MONTH(TODAY())+1,Bill_Cycle_Day)
The months of the year which have 31 days are: January, March, May, July, August, October and December. The months of the year which have 30 days are: April, June, September and November. February is an anomaly due to leap years.
December is a special scenario since if Next Bill Date falls in the new year, we would have to "reset" the month to 1 and add 1 to the Year: DATE(IF(MONTH(TODAY())=12,YEAR(TODAY())+1, YEAR(TODAY())), IF(MONTH(TODAY())=12,1, MONTH(TODAY())+1), Bill_Cycle_Day__c)
The formula for calculating leap year is as follows: OR(MOD( YEAR(TODAY() ), 400 ) = 0,AND(MOD( YEAR( TODAY() ), 4 ) = 0,MOD( YEAR( TODAY()), 100 ) != 0)) (reference)
IF(AND(
OR(MOD( YEAR( TODAY() ), 400 ) = 0,AND(MOD( YEAR( TODAY() ), 4 ) = 0,MOD( YEAR( TODAY()), 100 ) != 0)),
Bill_Cycle_Day__c>29,
OR(
AND(Bill_Cycle_Day__c<=DAY(Today()), MONTH(Today())+1=2),
AND(Bill_Cycle_Day__c>DAY(Today()), MONTH(Today())=2)
)),DATE(YEAR(TODAY()),2,29),
IF(
AND(Bill_Cycle_Day__c>28,
OR(
AND(Bill_Cycle_Day__c<=DAY(Today()), MONTH(Today())+1=2),
AND(Bill_Cycle_Day__c>DAY(Today()), MONTH(Today())=2)
)), DATE(YEAR(TODAY()),2,28),
IF(
AND(Bill_Cycle_Day__c>30,
OR(
AND(Bill_Cycle_Day__c<=DAY(Today()),
OR(MONTH(Today())+1=4, MONTH(Today())+1=6, MONTH(Today())+1=9, MONTH(Today())+1=11)),
AND(Bill_Cycle_Day__c>DAY(Today()),
OR(MONTH(Today())=4, MONTH(Today())=6, MONTH(Today())=9, MONTH(Today())=11))
)), DATE(YEAR(TODAY()), MONTH(TODAY()),30),
IF(Bill_Cycle_Day__c <= DAY(TODAY()), DATE(IF(MONTH(TODAY())=12,YEAR(TODAY())+1,YEAR(TODAY())), IF(MONTH(TODAY())=12, 1, MONTH(TODAY())+1),Bill_Cycle_Day__c), DATE(YEAR(TODAY()),MONTH(TODAY()),Bill_Cycle_Day__c))
)))
Other uses: Components of this formula can be used to calculate other dates such as birthdays, shipping dates, etc.