Why not to use DATEDIF() - Excel Tips and Tricks - video Dailymotion

ExcelTips247

Learn about why not to use DATEDIF() function.   These are the available units in DATEDIFF() function.  "Y" The number of complete years in the period.  "M" The number of complete months in the period.  "D" The number of days in the period.  "MD" The difference between the days in start_date and end_date. The months and years of the dates are ignored.  Important: We don't recommend using the "MD" argument, as there are known limitations with it. See the known issues section below.   "YM" The difference between the months in start_date and end_date. The days and years of the dates are ignored  "YD" The difference between the days of start_date and end_date. The years of the dates are ignored.   * IMPORTANT *  For "MD" Microsoft does not recommend using the "MD" argument, as there are known limitations with it. See the known issues section below.    These are the formulas used on the video.  Number of complete years in the period  =DATEDIF($A$2,$B$2,A5)  OR  =DATEDIF($A$2,$B$2,"Y")   Number of complete months in the period  =DATEDIF($A$2,$B$2,A6)  OR  =DATEDIF($A$2,$B$2,"M")   Number of days in the period.  =DATEDIF($A$2,$B$2,A7)  OR  =DATEDIF($A$2,$B$2,"D")   Difference between the days in start & end date.  Months & years of the dates are ignored.  =DATEDIF($A$2,$B$2,A8)  OR  =DATEDIF($A$2,$B$2,"MD")   Difference between the months in start & end date.  Days & years of the dates are ignored.  =DATEDIF($A$2,$B$2,A9)  OR  =DATEDIF($A$2,$B$2,"YM")   Difference between the days of start & end date.  Years of the dates are ignored.  =DATEDIF($A$2,$B$2,A10)  OR  =DATEDIF($A$2,$B$2,"YD")     DATEDIF function (Microsoft 365 support)  https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c    excel datedif missing,excel datedif not working,excel datedif today,datedif formula,how to use datedif in excel,  excel datedif minutes,excel datedif alternative,

Transcript

00:00If you're using dateDiv function, you have to be fully aware of its limitations associated with

00:04its use. Let me show you what I mean. Consider this table here. You got start date of January 1st

00:082022 and end date 14 May 2023. So the dateDiv function, you take the first argument as the

00:14start date, second argument as the end date, and the third argument is the unit of measures. So

00:17it's one year between the start and the end date. To calculate the month, this is how you do it as

00:22well. And you put the third argument as month and this is day. And the MD is to indicate the number

00:26of days between the two days, ignoring the month and the year. And the YM is calculating the number

00:32of months between the start and the end, ignoring the date and the year. And the YD is to calculate

00:37the number of days between the start and the end days, ignoring the years. But if you look at the

00:41different set of dates, 31st January 2022, 1st of May 2023, the date difference ignoring the year and

00:46the month is zero, which is obviously not correct. But if you look at this set of dates here, the date

00:50difference between these two dates are one, which is also not correct. So just be aware of it. Even

00:54Microsoft don't recommend you to use date div function along with the unit MD.

Why not to use DATEDIF() - Excel Tips and Tricks

Category

Transcript

Comments

Recommended