If you want to determine the number of months between two dates, you can use this DATEDIF function. Please enter this formula =DATEDIF(B1,B2,'m') into a blank cell (B1 stands for the start date and B2 indicates the end date), then press Enter key and you will get the number of months between the two given dates, see screenshot.
DATEDIF is a hidden function in Excel. As the name suggests the job of this function is to calculate the difference between two given dates.I have referred this function as hidden because, for some reason Microsoft has decided not to document this function. And because of this you won’t find this function in the Formula Tab.To verify this, try and type =DATE in any cell. You will see, Excel enlists all the functions that start with the word “date” but it doesn’t shows DATEDIF.Please note that, DATEDIF Function in Excel is totally different from the DATEDIFF (Notice the extra ‘F’) Function in VBA.
Syntax of Excel DATEDIF function:Learning the syntax of DATEDIF function is very important. Because for applying this function you need to know its complete syntax as Excel won’t provide you any help on this.The Syntax of DATEDIF function is as follows:=DATEDIF(StartDate, EndDate, Unit)Here, ‘ StartDate’ is the starting date of the period that you wish to calculate. ‘ StartDate’ can be entered as a string within double quotes (like: “”), it can also be entered as a serial number, as Excel internally treats dates as serial numbers (for example: the number 41557 represents “”, if you are using the 1900 date system), or you can also supply the dates as a formula (like: =DATE(2013,10,10)).‘ EndDate’ is the last date of the period that you wish to calculate.
Similar to ‘ StartDate’, ‘ EndDate’ can also be entered as a string, number or a formula.‘ Unit’ specifies the interval by which you want the difference to be returned. There are 6 ‘ Units’, which are described as under: UNITRETURNS“Y”Returns the period difference as complete years.“M”Returns the period difference as complete months.“D”Returns the number of days in the period.“MD”Returns the difference between the days in ‘StartDate’ and ‘EndDate’. Here the months and years of the dates are ignored.“YM”Returns the difference between the months in ‘StartDate’ and ‘EndDate’. Here the days and years of the dates are ignored“YD”Returns the difference between the days of ‘StartDate’ and ‘EndDate’. Here the years of the dates are ignored.How to Use Excel DATEDIF function:Now, let’s understand how to use DATEDIF function in excel.Objective: Let’s, consider our objective is to find the number of days from 14 April 1912 (The day on which Titanic Sank) till todays date.So, we will try to apply the DATEDIF formula.‘StartDate’: In this case our ‘ StartDate’ will be: 14 April 1912.‘EndDate’: EndDate will be today’s date. So, instead of entering the today’s date manually we will use the Today function.‘Unit’: As we want to find the number of days between the period. So, the ‘ Unit’ will be “d”.This formula results into: 36910 days.
Some Important points about DATEDIF Formula:. DATEDIF throws a #NUM error is StartDate is greater than ‘ EndDate’. It throws a #Value error if anyone of the date arguments is invalid. It also throws a #NUM error if the ‘ Unit’ is invalid.Few Examples of DATEDIF Function:Now, let’s move to some examples of DATEDIF function:Example 1: Write a formula to calculate the total number of years from 1/1/2001 to 1/1/2007.
This can be simply done by using the below formula:=DATEDIF(',','y')And the answer is 6.Example 2: Write a formula to calculate the number of complete months from 1/1/2001 to 1/1/2007.The formula that we are going to user here is:=DATEDIF(',','m')The answer is 72 i.e. 6 years X 12 months in each year =72Example 3: Write a formula to calculate your age in years, months and days.For finding the age we will need birth date of a person. In our example we will take the birth date as So, we will use a formula =DATEDIF(',TODAY,'y')&' years,'& DATEDIF(',TODAY,'ym') &' month(s), '& DATEDIF(',TODAY,'md')&' Days'The need to supress zero values:In Example 3 we have seen how DATEDIF function can be used of calculating age.
Excel worksheets can contain all types of different data, including formulas (like one that lets you ), text, numbers, pictures and dates. Dates are included in a number of different types of spreadsheets, especially those generated by databases, but many Excel users might find them to be more for reference purpose than having any type of added value.But Excel can actually perform some functions with the dates in your spreadsheet, including determining the number of days that have passed between two dates in your spreadsheet. So, for example, if you have a report that is pulling the total number of units sold on two separate dates, you could have Excel determine the number of days between those dates and calculate an average number of sales per date. You can learn the formula that will find the number of days between two dates in our guide below. Find the Number of Days Between Two Dates in Excel 2013The tutorial below is going to show you how to use an Excel formula to determine the number of days between two dates.
It will assume that the dates are stored as values in separate cells of your Excel spreadsheet. The result of the formula will be one single number, which represents the number of days between the two specified dates.Step 1: Open your spreadsheet in Excel 2013.Step 2: Click inside the cell where you want to display the number of days between your two dates. Step 3: The formula that we are using is called DATEDIF, and the syntax of the formula is =DATEDIF(startdate, enddate, interval).
The values that you will use are:startdate – the cell containing the earliest date that you are comparing. In my example image below, that is cell B2.enddate – the cell containing the later date that you are comparing. In my example below, that is cell B3.interval – the type of date unit that you will be measuring. You can choose between Y (year), M (month), and D (day). In my example below I am using “D”, because I want to determine the number of days between the two dates. Using “Y” or “M” would return the number of years or months between those two dates, respectively.The formula that I use in the image below is =DATEDIF(B2, B3, “D”).
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2023
Categories |