Excel DATEDIFF Function

This presentation is developed to impart understanding of Microsoft Excel's one of the most unique and powerful function DATEDIF. Which helps in calculating difference between dates.

1. Working with Dates using Microsoft Excel & DateDif Function

2. Introduction to Excel Dates Internally Microsoft Excel stores dates as numbers You can perform simple addition & subtraction calculations using + & - operators You can type dates either in the format of mm/ dd / yyyy (if your regional settings is set to American date) or dd /mm/ yyyy (if it is in British format) But the safest way is to type ddmmmyy i.e. 10sep09 will automatically converted by excel to a date representing 10-Sep-09

3. Introduction to Excel Dates To insert a date which automatically updates you can use a built in function: =DATE() If you want to enter a static current date you can also use the following shortcut key: Ctrl + ; that is press control key hold it down and along with that press ; key

4. Calculating Difference in Days – Example 1 Suppose if I want to calculate my age in days i.e. how many days I have spent till my birth in this world and if today’s date is 07-Oct-09, I can use the minus operator to do that: My date of birth Today’s date I can use – minus operator This represents number of days I am alive

5. But what about difference in months & years!

6. Introduction to DATEDIF function The answer to the question about month & year is DATEDIF You can calculate difference of months & years between two dates by using DATEDIF It’s one of the functions which is very rarely found in the Microsoft’s documentation for some good reasons

7. Syntax of DATEDIF = DATEDIF ( start_date , end_date , “interval” ) The function have 3 parameters start_date : This is the date from which you want to calculate the difference this date should be an older date than the end_date . end_date : This date is the date to which you want to calculate the difference, it should be higher than the start_date . “interval” : As the syntax shows it should be included in “ … “ inverted commas, number of options in next slide. 7

8. “interval” You can have any of the following values for the “interval” parameter: “d” : This parameter is used if you want difference in days “m” : This parameter is used if you want difference in months “y”: This parameter is used if you want difference in years “ md ” : This parameter is used if you want difference in days over months “ ym ” : This parameter is used if you want difference in months over year

9. Calculating Difference of Months & Years – Example 2 This says that I am 31 years 0 months & 23 days old d=Days m=Months y=Years Functions Results ym =Months over Year md =Days over Month

10. Concatenating results / combining results – Example 3 Say we want that Excel should display all the result in one cell i.e. 31 years 0 months & 23 days I can get help from & ampersand operator Text are enclosed in “ … “ inverted commas i.e. “I am “ To attach any text with formula value we put & ampersand sign outside the “ … “ inverted commas DATEDIF function itself which will return the required values

11. Conclusion Microsoft Excel treats dates as numbers You can perform addition & subtraction operations on dates When you type date it will convert the date as per the regional settings of your computer you can play safe by typing dates as mmdddyy 10oct09 If you want to calculate difference of dates for the intervals of months or years or even days over months and months over year you have a function DATEDIF which you can use Please visit: www.exceladvise.com For more Please comment if you like it, or have any suggestions. 11 Photo Courtesy – www.freedigitalphotos.net