如何計算兩個日子之間有多少年／月／日？
Recently, I got enquired about function for calculating number of days from one date to another date. DATEDIF is on the top of my mind. ：）
Interestingly this function is not commonly known probably because it won’t show up in the formula bar, even you type exact the DATEDIF in the formula bar… I still don’t know why actually.
Anyway, this simple function is quite useful.
Here’s the Syntax
DATEDIF(start_date,end_date,unit)
where start_date and end_date are quite straight forward, right?
Still a reminder for you: “The end_date argument must be a date that occurs after start_date. The day of the ending date is not counted in the final result.” This is extracted from the HELP menu.
“Unit” may require a little description:
| “Y” – Number of FULL YEAR （from start date to end date) |
| “YM” – Number of FULL MONTH regardless of Year |
| “MD” – Number of DAY regardless of Year and Month |
| “YD” – Number of DAY regardless of Year |
| “M” – Number of FULL MONTH |
| “D” – Number of DAY |
Example gives better explanation:
#NUM! error because start_date occurs before end_date OR incorrect Unit used.
Let’s try it on your own! 🙂
wmfexcel 
I have a very good formula for this (All in One). First table A2 and B2 gives you this:
“3 years, 0 months, 0 days”
Formula:
=YEAR($B$2)-YEAR(A2)-IF(OR(MONTH($B$2)<MONTH(A2),AND(MONTH($B$2)=MONTH(A2),
DAY($B$2)<DAY(A2))),1,0)&” years, “&MONTH($B$2)-MONTH(A2)+IF(AND(MONTH($B$2)
<=MONTH(A2),DAY($B$2)<DAY(A2)),11,IF(AND(MONTH($B$2)<MONTH(A2),DAY($B$2)
LikeLike
for some reason formula posted with space.
I’ll try again:
=YEAR($B$2)-YEAR(A2)-IF(OR(MONTH($B$2)<MONTH(A2),AND(MONTH($B$2)=MONTH(A2),DAY($B$2)<DAY(A2))),1,0)&” years, “&MONTH($B$2)-MONTH(A2)+IF(AND(MONTH($B$2)<=MONTH(A2),DAY($B$2)<DAY(A2)),11,IF(AND(MONTH($B$2)<MONTH(A2),DAY($B$2)>=DAY(A2)),12,IF(AND(MONTH($B$2)>MONTH(A2),DAY($B$2)<DAY(A2)),-1)))&” months,”&$B$2-DATE(YEAR($B$2),MONTH($B$2)-IF(DAY($B$2)<DAY(A2),1,0),DAY(A2))&” days”
LikeLike
Thanks for your input, Igor.
LikeLike
You can basically make your formula more independent by combining all arguments into one formula like this:
=DATEDIF(A32,B32,”Y”)&” Year(s) ” & DATEDIF(A32,B32,”YM”) & ” Month(s) ” & DATEDIF(A32,B32,”MD”) & ” Day(s) “
LikeLike
Hi Igor,
Yes, you are right. We can combine the formula and remove the helper columns. I did it step by step so that can be read and understood easier.
Cheers,
LikeLike
I actually like your formula better than mine. It’s shorter, cleaner and smarter:
=DATEDIF(A32,B32,”Y”)&” years ” & DATEDIF(A32,B32,”YM”) & ” months ” & DATEDIF(A32,B32,”MD”) & ” days”
The only problem I found in Excel 2010 when I type =datedif nothing comes up like with regular formulas until you put (, then Excel shows =DATEDIF(). Maybe that happens because its not available in Office 2010?
LikeLike
Hi Igor,
That’s because the DATEDIF function in Excel is somehow an “unofficial” function. You won’t find it in the function library.
And IF you are “lucky” enough, you may encounter a bug when using DATEDIF.
You may be interesting to read the following by Microsoft MVP for Excel – Rick Rothstein
http://www.excelfox.com/forum/f22/recommendation-do-not-use-the-undocumented-datedif-function-321/
which I found quite insightful!
Cheers,
LikeLike
Pingback: Calculate number of a specific day between two dates | wmfexcel
Pingback: Calculating difference between dates in days