Slide 1: Click to edit Master subtitle style
Working with Dates using Microsoft Excel & DateDif Function
11/6/10
Slide 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.
11/6/10
Slide 3: Introduction to Excel Dates
•
To insert a date which automatically updates you can use a built in function: 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
=DATE()
•
11/6/10
Slide 4: •
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
Calculating Difference in Days – Example 1
This represents number of days I am 11/6/10 alive
I can use – minus operator
Slide 5: But what about difference in months & years!
11/6/10
Slide 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
•
•
11/6/10
Slide 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 11/6/10 77 included in “ … “ inverted commas, number of
Slide 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
11/6/10
Slide 9: Calculating Difference of Months & Years – Example 2
d=Days
Functions
Results
m=Months
This says that I am 31 years 0 months & 23 days old
y=Years
ym=Months over Year md=Days over Month
11/6/10
Slide 10: •
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 “
Concatenating results / combining results – Example 3
To attach any text with formula value we put & ampersand sign outside the “ … “ inverted commas 11/6/10
DATEDIF function itself which will return the required values
Slide 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 comment if you like it, or have any 11/6/10 suggestions.
Please visit: Courtesy – 1111 Photo www.freedigitalphotos.net
