Quite often in Excel you will want to subtract one date from another. This could be to check length of service of an employee, how long a customer has taken to pay a bill or the length of a project.
I find the most versatile function to use for this is DATEDIF. This actually stands for DATE DIFFERENCE and can be used in Excel 97 onwards.
It is a somewhat undocumented function and is not listed in the formula tab in Excel 2007. This formula must be manually typed into a cell on your work book rather than in a dialog box that can be used in other Excel functions.
The syntax for the function is:-
=DATEDIF(start_date, end_date, unit). There are three arguments for the functions which are:-
start_date- the first or starting date
end_date- the second or end date
unit- this tells the function to find the number of complete days (D), months (M) or years (Y) between the two dates.
The codes that can be used for the units are
Enter the following dates in cells A2 and B2 respectively 01/03/2008 and 01/05/2011. In C2 on your worksheet type =DATEDIF(A2,B2,"M") this should give you an answer of 38.
This gives the number of months, but, what if you need to find the length of service for one of your employees you will need to know the years, months and days. All you need to do is be a little clever with your formula.
The same formula to get the years, months and days for the same dates would look like this
=DATEDIF(A2,B2,"y")&"Years"&DATEDIF(A2,B2,"ym")&"Months"&DATEDIF(A2,B2, md")&"Days"
This would then give a result of 3 years, 2 Months and 0 Days. If you are using a text string for your unit then it needs to be enclosed in "", if it is referenced via a cell, then it does not.
Should your formula show a result of #NUM! then it indicates that your start_date is larger or earlier than your end_date.
I find the most versatile function to use for this is DATEDIF. This actually stands for DATE DIFFERENCE and can be used in Excel 97 onwards.
The syntax for the function is:-
=DATEDIF(start_date, end_date, unit). There are three arguments for the functions which are:-
start_date- the first or starting date
end_date- the second or end date
unit- this tells the function to find the number of complete days (D), months (M) or years (Y) between the two dates.
The codes that can be used for the units are
- ul>
- D- days- the number of days between the dates
- M - months- the number of months between the dates
- Y- years- the number of complete years between the dates
- YM-complete calendar months excluding years
- YD- complete calendar days excluding years
- MD - complete calendar days excluding months and years
Enter the following dates in cells A2 and B2 respectively 01/03/2008 and 01/05/2011. In C2 on your worksheet type =DATEDIF(A2,B2,"M") this should give you an answer of 38.
This gives the number of months, but, what if you need to find the length of service for one of your employees you will need to know the years, months and days. All you need to do is be a little clever with your formula.
The same formula to get the years, months and days for the same dates would look like this
=DATEDIF(A2,B2,"y")&"Years"&DATEDIF(A2,B2,"ym")&"Months"&DATEDIF(A2,B2, md")&"Days"
This would then give a result of 3 years, 2 Months and 0 Days. If you are using a text string for your unit then it needs to be enclosed in "", if it is referenced via a cell, then it does not.
Should your formula show a result of #NUM! then it indicates that your start_date is larger or earlier than your end_date.
Want more Excel Tips and Tricks? Look no further! BJ Johnston has been an advanced Excel user for 15 years and is the creator of http://www.howtoexcelatexcel.com a site that shares Excel tips and tricks with it's enthusiastic members. Join in the discussion, where users are working smarter and faster with Excel and don't forget to sign up for a FREE newsletter and as a bonus receive a FREE EBook- 50 Top Tips and Tricks.
Article Source: http://EzineArticles.com/?expert=BJ_Johnston 
0 comments:
Post a Comment