Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Calculate Months

0

A2 is PAYMENT DUE DATE

B2 is PAYMENT RECEIVED DATE

C2 is MONTHS PASS DUE

i need a formula to insert in cell C2 that will calculate the number of months the payment is passdue, but if B2 is blank, then I want it to calculate the number of months pass due using the current date.

i used the DATEIF(A2,B2,"M") to calculate the months pass due but I need help adding the condition that if B2 is blank, to then calculate the number of months using the current date. I know that if I enter =TODAY () in a cell it will display the current date in that cell. I Just don't know how to link the formulas to make it do what I need. Please help!

excel
MS Office Version: 2007
posted by: PLN_learning (rep: 2) on Fri Sep 21, 2018 at 1:16 am
Answer
Discuss

Answers

0
Selected Answer

Please try this formula.

=IFERROR(DATEDIF($A2,IF($B2="",TODAY(),$B2), "M"),"Not yet due")

The point is that DATEDIF can't calculate the difference if A2 if later than B2. An error will occur. The above formula catches the error and issues a message. As an alternative you might simple reverse the two dates in case of the first formula throwing an error.

=IFERROR(DATEDIF($A3,IF($B3="",TODAY(),$B3), "M"),DATEDIF(IF($B3="",TODAY(),$B3),$A3, "M")*-1)
posted by: Variatus (rep: 4889) on Fri Sep 21, 2018 at 6:01 am
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login