To count months between two dates may become one of the trickiest calculations. It may not be too obvious. There is a function which can make this task easy but it is hidden by Microsoft.
The function is DATEDIF. You can also remember it as Date Difference.
There is a new function to calculate months in MS Excel – the MONTHS function. But the result of this feature is not so satisfying. The reason is that it only consider month during calculation, and not the difference of years.
To calculate months between two dates in Excel, you should use the function – DATEDIF. You can use the same method for Google sheets.
Step 1: Open MS Excel and enter the formula into a blank cell. For entering the formula, type =DATEDIF (B1, B2,”m”)
Here B1 stands for First date and B2 stands for the End date. And “m” stands for months.
Step 2: Press ‘Enter’ key.
Note: Here, you can use “d” for days and “y” for years.
Formula: Round Up – =(YEAR(LDate)-YEAR(EDate))*12+MONTH(LDate)-MONTH(EDate)
Round Down – =IF(DAY(LDate)>=DAY(EDate),0,-1)+(YEAR(LDate)-YEAR(EDate)) *12+MONTH(LDate)-MONTH(EDate)
Here EDate = Earlier Date and LDate = Later Date
Note: Edit the round down formula in the cell. The formula is very long, and because of some reasons, it may be possible that the entire formula does not show in the formula bar.
Step 1: Open MS Excel and click on “Formulas.”
Step 2: Now click “Show Formulas.”
Step 3: Apply the formula and get the desired result.
- The DATADIF function only calculates full months. You should consider such information when you are calculating month difference between two dates because if you are a professional, you cannot take any chances and cannot make silly mistakes.
- One more thing one should keep in mind that the start date should not be more than the end date. Otherwise, it will show ERROR. Start date less than or equal to is fine.
- Many people count the months according to their wish, as you can either round up or round down. Mostly, when a month is partial, and before the date 15, people consider to do round down, and if the date is close to month end or after 15th, people think to round up. You should be clear on what method you are choosing.
- To calculate months between two dates in Excel or Google sheets, you should understand that both dates occur in the same year or dates occur in two different years.
As I have mentioned, apart from months, you can find days, weeks, or years also. Let us show you different formulas for the same.
- Days: If you want to find days between two dates then it is the simplest calculation. The method for counting days is =Days (A2, A1) and if you want to calculate it with the formula of DATADIF then it will go like this: =DATADIF (A1, A2, “D”)
- Weekdays: You can even calculate the number of weekdays. The formula for that goes like this: =NETWORKDAYS (startDate, endDate)
- Years: Let’s take two different methods for this – that is two separate formulas/functions. DATADIF and YEAR function. The data difference function is =DATADIF (A1, A2, “Y”) and if you want to use simple function – the YEAR function – use the formula like this: =YEAR(A2)-YEAR(A1)
Bonus tip: If you are a blogger or interested in such, there is one other function displaying date, time, or anything related to your blog post (i.e.,) ‘Timestamp.’ You can easily insert the timestamp in Google Sheets. It will let your readers know how old your post is.
That’s all for today. I hope this article is helpful to you and able to solve all your problems regarding month’s calculation in Excel and Google Sheets.
Latest posts by Azad Shaikh (see all)
- Best Raspberry Pi 3 Kits for Beginners - July 18, 2017
- Best Bolt Carrier Group of 2017 - July 10, 2017
- How to Remove a Watermark or Logo from Video: Top Instant Solutions! - July 3, 2017