Datedif Function Excel For Mac 2016



  1. Datedif Function Excel For Mac 2016 Free
  2. Datedif Function Excel For Mac 2016 Free
  3. Datedif Excel 2016 Missing
  4. Excel Datedif Function Missing
  5. Microsoft Excel For Mac

Note: DATEDIF automatically rounds down. To round up to the nearest month, see below. The mystery of DATEDIF. The DATEDIF function is a 'compatibility' function that comes from Lotus 1-2-3. For reasons unknown, DATEDIF is only documented in Excel 2000, and will not appear as a suggested function. Hi, I think I found a bug in Excel with the DATEDIF Function. I tested it with 2016 and 2010 Version and both had the same strange behaviour. After changing a cell reference, the Function isn't recognized as function any more and therefore not calculated any more. Datedif does it work in excel Mac 2016? Posted by 11 hours ago. Datedif does it work in excel Mac 2016? So basically I’ve been trying to.

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

Excel

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(Start_Date, End_Date, Unit)

Here, ‘Start_Date’ is the starting date of the period that you wish to calculate. ‘Start_Date’ can be entered as a string within double quotes (like: “10/10/2013”), it can also be entered as a serial number, as Excel internally treats dates as serial numbers (for example: the number 41557 represents “10/10/2013”, if you are using the 1900 date system), or you can also supply the dates as a formula (like: =DATE(2013,10,10)).

End_Date’ is the last date of the period that you wish to calculate. Similar to ‘Start_Date’, ‘End_Date’ 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 ‘Start_Date’ and ‘End_Date’. Here the months and years of the dates are ignored.
“YM”Returns the difference between the months in ‘Start_Date’ and ‘End_Date’. Here the days and years of the dates are ignored
“YD”Returns the difference between the days of ‘Start_Date’ and ‘End_Date’. Here the years of the dates are ignored.

How to Use Excel DATEDIF function

Datedif

Now, let’s understand how to use the 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 today’s date.

So, we will try to apply the DATEDIF formula.

‘Start_Date’: In this case our ‘Start_Date’ will be 14 April 1912.

‘End_Date’: End_Date will be today’s date. So, instead of entering 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 in 36910 days.

Some Important points about DATEDIF Formula

  • DATEDIF throws a #NUM error is Start_Date is greater than ‘End_Date’.
  • 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 the 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:

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('01/01/2001','01/01/2007','m')

The answer is 72 i.e. 6 years X 12 months in each year =72

Example 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 12/12/1986

So, we will use a formula

For
=DATEDIF('12/12/1986',TODAY(),'y')&' years,'&DATEDIF('12/12/1986',TODAY(),'ym') &' month(s), '&DATEDIF('12/12/1986',TODAY(),'md')&' Days'

The need to supress zero values

In Example 3 we have seen how DATEDIF function can be used of calculating age. However, if the number of years and/or months is 0, then the resultant of the formula looks strange.

Okay, I will try to explain this in detail.

Consider that we have to calculate age of a baby born on 12/12/2012.

So, will try to use the formula:

=DATEDIF('12/12/2012',TODAY(),'y')&' years,'&DATEDIF('12/12/2012',TODAY(),'ym') &' month(s), '&DATEDIF('12/12/2012',TODAY(),'md')&' Days'
Function

See, what the result of this formula is.

Doesn’t 0 years looks odd.

So, to fix this issue we will use the IF function along with Excel DATEDIF.

And the new formula will be:

=IF(DATEDIF('12/12/2012',TODAY(),'y') = 0, ', DATEDIF('12/12/2012',TODAY(),'y') &' years,')&IF(DATEDIF('12/12/2012',TODAY(),'ym') = 0, ', DATEDIF('12/12/2012',TODAY(),'ym') &' month(s), ')&IF(DATEDIF('12/12/2012',TODAY(),'md') = 0,',DATEDIF('12/12/2012',TODAY(),'md')&' Days')

I think now you all would agree, that the output of this new formula looks better than the old one.

Is DATEDIF a safe function?

I know this is a bit strange but many Excel Gurus say that it’s not good to use DATEDIF in your important workbooks. As this is an undocumented function and going forward there may be a chance that Microsoft may remove support for this function.

So, what’s the alternative to DATEDIF?

The alternative to Excel DATEDIF would be a User-defined function (UDF) that internally uses the VBA DATEDIFF function:

This UDF accepts three parameters:

  • Start_Date: The days from which the period begins.
  • End_Date: It is the last date of the period that you wish to calculate.
  • Unit: It specifies the interval by which you want the difference. Here the unit accepts following values.
ValueDescription
YYYYYear
QQuarter
MMonth
YDay of year
DDay
WWeekday
WWWeek
HHour
NMinute
SSecond

Example 4: Using this UDF to find the number of days between “01/01/2010” and “01/01/2012”.

To do this we will use the formula:

=xlDATEDIF('01/01/10','01/01/12','d')

And it results into 730.

So, this was all about Excel DATEDIF function. Do let me know in case you come across any issues while using this function.

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(Start_Date, End_Date, Unit)

Here, ‘Start_Date’ is the starting date of the period that you wish to calculate. ‘Start_Date’ can be entered as a string within double quotes (like: “10/10/2013”), it can also be entered as a serial number, as Excel internally treats dates as serial numbers (for example: the number 41557 represents “10/10/2013”, if you are using the 1900 date system), or you can also supply the dates as a formula (like: =DATE(2013,10,10)).

End_Date’ is the last date of the period that you wish to calculate. Similar to ‘Start_Date’, ‘End_Date’ 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 ‘Start_Date’ and ‘End_Date’. Here the months and years of the dates are ignored.
“YM”Returns the difference between the months in ‘Start_Date’ and ‘End_Date’. Here the days and years of the dates are ignored
“YD”Returns the difference between the days of ‘Start_Date’ and ‘End_Date’. Here the years of the dates are ignored.

How to Use Excel DATEDIF function

Now, let’s understand how to use the 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 today’s date.

So, we will try to apply the DATEDIF formula.

‘Start_Date’: In this case our ‘Start_Date’ will be 14 April 1912.

‘End_Date’: End_Date will be today’s date. So, instead of entering 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 in 36910 days.

Datedif Function Excel For Mac 2016 Free

Some Important points about DATEDIF Formula

  • DATEDIF throws a #NUM error is Start_Date is greater than ‘End_Date’.
  • 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

Datedif Function Excel For Mac 2016 Free

Now, let’s move to some examples of the 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:

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('01/01/2001','01/01/2007','m')

The answer is 72 i.e. 6 years X 12 months in each year =72

Example 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 12/12/1986

So, we will use a formula

=DATEDIF('12/12/1986',TODAY(),'y')&' years,'&DATEDIF('12/12/1986',TODAY(),'ym') &' month(s), '&DATEDIF('12/12/1986',TODAY(),'md')&' Days'

The need to supress zero values

In Example 3 we have seen how DATEDIF function can be used of calculating age. However, if the number of years and/or months is 0, then the resultant of the formula looks strange.

Okay, I will try to explain this in detail.

Consider that we have to calculate age of a baby born on 12/12/2012.

Datedif Excel 2016 Missing

So, will try to use the formula:

=DATEDIF('12/12/2012',TODAY(),'y')&' years,'&DATEDIF('12/12/2012',TODAY(),'ym') &' month(s), '&DATEDIF('12/12/2012',TODAY(),'md')&' Days'

See, what the result of this formula is.

Doesn’t 0 years looks odd.

So, to fix this issue we will use the IF function along with Excel DATEDIF.

And the new formula will be:

=IF(DATEDIF('12/12/2012',TODAY(),'y') = 0, ', DATEDIF('12/12/2012',TODAY(),'y') &' years,')&IF(DATEDIF('12/12/2012',TODAY(),'ym') = 0, ', DATEDIF('12/12/2012',TODAY(),'ym') &' month(s), ')&IF(DATEDIF('12/12/2012',TODAY(),'md') = 0,',DATEDIF('12/12/2012',TODAY(),'md')&' Days')

I think now you all would agree, that the output of this new formula looks better than the old one.

Is DATEDIF a safe function?

I know this is a bit strange but many Excel Gurus say that it’s not good to use DATEDIF in your important workbooks. As this is an undocumented function and going forward there may be a chance that Microsoft may remove support for this function.

So, what’s the alternative to DATEDIF?

Excel Datedif Function Missing

The alternative to Excel DATEDIF would be a User-defined function (UDF) that internally uses the VBA DATEDIFF function:

This UDF accepts three parameters:

  • Start_Date: The days from which the period begins.
  • End_Date: It is the last date of the period that you wish to calculate.
  • Unit: It specifies the interval by which you want the difference. Here the unit accepts following values.
ValueDescription
YYYYYear
QQuarter
MMonth
YDay of year
DDay
WWeekday
WWWeek
HHour
NMinute
SSecond

Example 4: Using this UDF to find the number of days between “01/01/2010” and “01/01/2012”.

To do this we will use the formula:

=xlDATEDIF('01/01/10','01/01/12','d')

Microsoft Excel For Mac

And it results into 730.

So, this was all about Excel DATEDIF function. Do let me know in case you come across any issues while using this function.