Excel Difference In 2 Dates?

I need a formula that will calculate going from one month to another and the number of whole months in between (without taking into account the actual # of days between two dates.   So if start date is 6/1/2020 and end date is 7/31/2020 i want excel to know i've gone from June 2020 to July 2020 and that's 1 month (so return a 1)   If start date is 6/30/2020 and end date is 7/1/2020, i want excel to still know i've gone from June 2020 to July 2020 and return 1.  Examples below.  I can return a number for the month and subtract that way no problem, but there are multiple years included as well.

Thanks in advance for your help.

Attachment image

2 Answers

Relevance
  • 1 month ago

    you can use the DATEDIF() function and just always ensure Start Date is the 1st day of the month.  if your 1st 6/1/2020 is in cell A2 then use this formula

    =DATEDIF(A2-DAY(A2)+1,B2,"m")

    it should just work when you drag it down.

    let me know how it works.

  • opurt
    Lv 7
    1 month ago

    You can use the YEAR( ) and MONTH( ) functions together for this . The formula would be something like  =12*(YEAR(B2)-YEAR(B1)) + MONTH(B2)-MONTH(B2). 

Still have questions? Get your answers by asking now.