Menu
I have a worksheet that has a start date and end date. I need to get the number of months between these dates. I used the formula =MONTH(B1)-MONTH(A1) to get that #. However this formula fails when my dates span multiple years - if start date is 1/1/2014 and end date is 1/1/2015, then it returns a "1", when I'd like to get a "12".
I then tried DATEDIF(A1,B1,"m") to get the # of months between the dates, and that worked. My issue, however, can be summarized by the following example:
Start Date: 1/5/2014
End Date: 3/1/2014
"MonthDif": 2
DateDif: 1
Start Date: 1/5/2014
End Date: 3/10/2014
"MonthDif": 2
DateDif: 2
As you can see, the DATEDIF function returns the # of complete months, while my month - month function returns the number of months "occupied" by the difference between the start and end dates, regardless if they are complete months.
I need the number of months, regardless if full months, over any date period! Basically, I need exactly what the MONTH(B1)-MONTH(A1) returns, except it will work over multiple years.
Also, I was considering designing a custom VBA function to achieve the above. If anyone has any suggestions there.
284
Let us understand the working of a MONTH in excel by some examples. serial_number: a valid date for which the month number is to be identified. The input date must be a valid Excel date. The dates in Excel are stored as serial numbers. For example, the date Jan 1, 2010, is equal to the serial number 40179 in Excel.
The results returned by DATEDIF and YEAR/MONTH formulas are not always identical because they operate based on different principles. The Excel DATEDIF function returns the number of complete calendar months between the dates, while the YEAR/MONTH formula operates on months' numbers.
Similarly to counting days, the Excel DATEDIF function can compute the number of months between two dates that you specify. Depending on the unit you supply, the formula will produce different results.
MONTH function in Excel will return 16 for January. The MONTH function returns the month of the given date or serial number. Excel MONTH Function is given #VALUE! Error when it cannot recognize the date.
If you use DATEDIF but always count from the 1st of the first month you'll get what you need, e.g. if you use =A1-DAY(A1)+1 that will give you the first of the A1 month, so use that in DATEDIF like this
=DATEDIF(A1-DAY(A1)+1,B1,"m")
83
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
