DATEDIF Formula is very useful to calculate age or difference between two dates in excel
The DATEDIF formula computes the difference between two dates in a different manners which is very different to just subtraction of two dates like it can calculate the number of years, months, or days between the two dates. this formula is not available in already defined formulas of excel, look at below snapshot.
but it can be available by typing DATEDIF.
Now we learn that how to use DATEDIF :
=DATEDIF(Date1, Date2, Interval)
Where:
Date1 is the first date,
Date2 is the second date,
Interval is type to return.
Note :- Date1 should be less than Date2, Otherwise this function will not work
Important Description:-
"m" Months :- Complete calendar months between the dates.
"d" Days :- Number of days between the dates.
"y" Years :- Complete calendar years between the dates.
"ym" Months Excluding Years :- Complete calendar months between the dates as if they were of the same year.
"yd" Days Excluding Years :- Complete calendar days between the dates as if they were of the same year.
"md" Days Excluding Years :- And Months Complete calendar days between the dates as if they were of the same month and same year.
If you are including the Interval string directly within the formula, you must enclose it in double quotes:
=DATEDIF(Date1,Date2,"m")
Supressing Zero Value Components
In typical usage to calculate the number of years, months, and days between two dates, you would use a formula such as the following, where A1 is the start date and B1 is the end date:
=DATEDIF(A1,B1,"y")&" years "&DATEDIF(A1,B1,"ym")&" months "&DATEDIF(A1,B1,"md")
&" days"
This will return a string such as
12 years 8 months 14 days
However, if the number of years and/or months is 0, you'll get a string like
0 years 0 months 14 days
0 years 3 months 14 days
If you want to suppress the 0 values, and return a result such as
8 months 14 days
or
14 days
where the 0-valued components are not displayed, use a formula like the following:
=IF(DATEDIF(A1,B1,"y")=0,"",DATEDIF(A1,B1,"y")&" years ")&IF(DATEDIF(A1,B1,"ym")=0,"",
DATEDIF(A1,B1,"ym")&" months ")&DATEDIF(A1,B1,"md")&" days"
This will display only the date components whose value is greater than 0. The day value will always be displayed, but the year and/or the month value may be suppresed.
Examples
The follow are some examples of the DATEDIF function.
Date1: 1-Jan-2007
Date2: 10-Jan-2007
Interval: d
Result: 9
Explanation:
There are 9, not 10, calendar days between these two dates.
Date1: 1-Jan-2007
Date2: 31-Jan-2007
Interval: m
Result: 0
Explanation:
There 0 complete calendar months between the two dates.
Date1: 1-Jan-2007
Date2: 1-Feb-2007
Interval: m
Result: 1
Explanation:
There are 1 complete months between the two dates.
Date1: 1-Jan-2007
Date2: 28-Feb-2007
Interval: m
Result: 1
Explanation:
There are 1 complete months between the two dates.
Date1: 1-Jan-2007
Date2: 31-Dec-2007
Interval: d
Result: 364
Explanation:
There are 364 days between the two dates.
Date1: 1-Jan-2007
Date2: 31-Jan-2007
Interval: y
Result: 0
Explanation:
There are 0 complete years between the dates
Date1: 1-Jan-2007
Date2: 1-July-2008
Interval: d
Result: 547
Explanation:
There are 547 days between the two dates.
Date1: 1-Jan-2007
Date2: 1-July-2008
Interval: m
Result: 18
Explanation:
There are 18 months between the two dates.
Date1: 1-Jan-2007
Date2: 1-July-2008
Interval: ym
Result: 6
Explanation:
There are 6 months between the two dates if the dates are considered to have the same year. The year it taken from Date1, not Date2. This makes a difference when one year is a leap year. Since 2007 is not a leap year, 29-Feb is not counted. See DATEDIF And Leap Years below.
Date1: 1-Jan-2007
Date2: 1-July-2008
Interval: yd
Result: 181
Explanation:
There are 181 days between the dates if the dates are considered to have the same year. The year it taken from Date1, not Date2. This makes a difference when one year is a leap year. Since 2007 is not a leap year, 29-Feb is not counted. See DATEDIF And Leap Years below.
Date1: 1-Jan-2008
Date2: 1-July-2009
Interval: yd
Result: 182
Explanation:
There are 182 days between the dates if the dates are considered to have the same year. This result is 182 not 181 since Date1 is a leap year and thus 29-Feb is counted.
Date1: 1-Jan-2007
Date2: 31-Jan-2007
Interval: md
Result: 30
Explanation:
There are 30 days between the 1st and 31st of the dates when both dates' month and years are considered to be the same.
DATEDIF And Leap Years
When calculating date intervals, DATEDIF uses the year of Date1, not Date2 when calculating the yd, ym and md intervals. For example,
=DATEDIF(Date1,Date2,"md")
returns 28 for Date1 = 1-Feb-2007 and Date2 = 1-March-2009. Since Date1 is not a leap year, the date 29-Feb is not counted. But the same formula with Date1 = 1-Feb-2008 returns 29, since Date1 is a leap year and therefore the date 29-Feb is counted.
Calculating Age
You can use the DATEDIF to calculate a person's age. For example, the formula below will calculate the age of a person as of the current date, where BirthDate is the person's date of birth.
=DATEDIF(BirthDate,TODAY(),"y")&" years "&DATEDIF(BirthDate,TODAY(),"ym")&" months "&DATEDIF(BirthDate,TODAY(),"md")&" days"
If you like this post, please like it. for any query or feedback please mention it on comment.
Thank You
The DATEDIF formula computes the difference between two dates in a different manners which is very different to just subtraction of two dates like it can calculate the number of years, months, or days between the two dates. this formula is not available in already defined formulas of excel, look at below snapshot.
but it can be available by typing DATEDIF.
Now we learn that how to use DATEDIF :
=DATEDIF(Date1, Date2, Interval)
Where:
Date1 is the first date,
Date2 is the second date,
Interval is type to return.
Note :- Date1 should be less than Date2, Otherwise this function will not work
Important Description:-
"m" Months :- Complete calendar months between the dates.
"d" Days :- Number of days between the dates.
"y" Years :- Complete calendar years between the dates.
"ym" Months Excluding Years :- Complete calendar months between the dates as if they were of the same year.
"yd" Days Excluding Years :- Complete calendar days between the dates as if they were of the same year.
"md" Days Excluding Years :- And Months Complete calendar days between the dates as if they were of the same month and same year.
If you are including the Interval string directly within the formula, you must enclose it in double quotes:
=DATEDIF(Date1,Date2,"m")
Supressing Zero Value Components
In typical usage to calculate the number of years, months, and days between two dates, you would use a formula such as the following, where A1 is the start date and B1 is the end date:
=DATEDIF(A1,B1,"y")&" years "&DATEDIF(A1,B1,"ym")&" months "&DATEDIF(A1,B1,"md")
&" days"
This will return a string such as
12 years 8 months 14 days
However, if the number of years and/or months is 0, you'll get a string like
0 years 0 months 14 days
0 years 3 months 14 days
If you want to suppress the 0 values, and return a result such as
8 months 14 days
or
14 days
where the 0-valued components are not displayed, use a formula like the following:
=IF(DATEDIF(A1,B1,"y")=0,"",DATEDIF(A1,B1,"y")&" years ")&IF(DATEDIF(A1,B1,"ym")=0,"",
DATEDIF(A1,B1,"ym")&" months ")&DATEDIF(A1,B1,"md")&" days"
This will display only the date components whose value is greater than 0. The day value will always be displayed, but the year and/or the month value may be suppresed.
Examples
The follow are some examples of the DATEDIF function.
Date1: 1-Jan-2007
Date2: 10-Jan-2007
Interval: d
Result: 9
Explanation:
There are 9, not 10, calendar days between these two dates.
Date1: 1-Jan-2007
Date2: 31-Jan-2007
Interval: m
Result: 0
Explanation:
There 0 complete calendar months between the two dates.
Date1: 1-Jan-2007
Date2: 1-Feb-2007
Interval: m
Result: 1
Explanation:
There are 1 complete months between the two dates.
Date1: 1-Jan-2007
Date2: 28-Feb-2007
Interval: m
Result: 1
Explanation:
There are 1 complete months between the two dates.
Date1: 1-Jan-2007
Date2: 31-Dec-2007
Interval: d
Result: 364
Explanation:
There are 364 days between the two dates.
Date1: 1-Jan-2007
Date2: 31-Jan-2007
Interval: y
Result: 0
Explanation:
There are 0 complete years between the dates
Date1: 1-Jan-2007
Date2: 1-July-2008
Interval: d
Result: 547
Explanation:
There are 547 days between the two dates.
Date1: 1-Jan-2007
Date2: 1-July-2008
Interval: m
Result: 18
Explanation:
There are 18 months between the two dates.
Date1: 1-Jan-2007
Date2: 1-July-2008
Interval: ym
Result: 6
Explanation:
There are 6 months between the two dates if the dates are considered to have the same year. The year it taken from Date1, not Date2. This makes a difference when one year is a leap year. Since 2007 is not a leap year, 29-Feb is not counted. See DATEDIF And Leap Years below.
Date1: 1-Jan-2007
Date2: 1-July-2008
Interval: yd
Result: 181
Explanation:
There are 181 days between the dates if the dates are considered to have the same year. The year it taken from Date1, not Date2. This makes a difference when one year is a leap year. Since 2007 is not a leap year, 29-Feb is not counted. See DATEDIF And Leap Years below.
Date1: 1-Jan-2008
Date2: 1-July-2009
Interval: yd
Result: 182
Explanation:
There are 182 days between the dates if the dates are considered to have the same year. This result is 182 not 181 since Date1 is a leap year and thus 29-Feb is counted.
Date1: 1-Jan-2007
Date2: 31-Jan-2007
Interval: md
Result: 30
Explanation:
There are 30 days between the 1st and 31st of the dates when both dates' month and years are considered to be the same.
DATEDIF And Leap Years
When calculating date intervals, DATEDIF uses the year of Date1, not Date2 when calculating the yd, ym and md intervals. For example,
=DATEDIF(Date1,Date2,"md")
returns 28 for Date1 = 1-Feb-2007 and Date2 = 1-March-2009. Since Date1 is not a leap year, the date 29-Feb is not counted. But the same formula with Date1 = 1-Feb-2008 returns 29, since Date1 is a leap year and therefore the date 29-Feb is counted.
Calculating Age
You can use the DATEDIF to calculate a person's age. For example, the formula below will calculate the age of a person as of the current date, where BirthDate is the person's date of birth.
=DATEDIF(BirthDate,TODAY(),"y")&" years "&DATEDIF(BirthDate,TODAY(),"ym")&" months "&DATEDIF(BirthDate,TODAY(),"md")&" days"
If you like this post, please like it. for any query or feedback please mention it on comment.
Thank You
No comments:
Post a Comment