#dateformula

[systemwebtech]

View this post on Instagram

For more follow @systemwebtech YouTube (link in bio).. . . . #excelshortcutkey #dateformula #excelfunction #excelmanagement #excel #computer #career #onlinecourses #onlinejobs #dataentry #datascience #dataanalyst #misinterviewquestion #excelinterviewquestion #exceltrick #excelfunction #office #microsoftoffice #exceldata #excelentry #excelcareer #naukri #tutorial #tutorialhindi #manager #sales #exceltrick #advanceexcel #basicexcel #exceltraining #onlineexceltraining #onlineexceltrainer (at India) https://www.instagram.com/p/CdBXsnyjFew/?igshid=NGJjMDIxMWI=

[samgottheflow]

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. 

Considerations:

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.

Steps to Derive 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)

Final Formula: 

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.