Excel，如何计算当年月份数 如何用EXCEL计算两个日期之间相差的年数和月数？
\u5982\u4f55\u5728EXCEL\u4e2d\u8ba1\u7b97\u6708\u4efd\u4e4b\u5dee\u5728EXCEL\u4e2d\u8ba1\u7b97\u6708\u4efd\u4e4b\u5dee\u7684\u65b9\u6cd5\uff1a\u53ef\u4ee5\u4f7f\u7528DATEDIF\u51fd\u6570\u3002\u5177\u4f53\u4f7f\u7528\u65b9\u6cd5\uff1a\u5047\u8bbe\u8d77\u59cb\u65e5\u671f\u5199\u5728A1\u5355\u5143\u683c\uff0c\u7ed3\u675f\u65e5\u671f\u5199\u5728B1\u5355\u5143\u683c\uff0c\u5219\u516c\u5f0f\u53ef\u4ee5\u5728\u5355\u5143\u683c\u4e2d\u5199\u6210\u5199\u6210=DATEDIF(A1,B1,"m")\u3002
Microsoft Excel\u662fMicrosoft\u4e3a\u4f7f\u7528Windows\u548cApple Macintosh\u64cd\u4f5c\u7cfb\u7edf\u7684\u7535\u8111\u7f16\u5199\u7684\u4e00\u6b3e\u7535\u5b50\u8868\u683c\u8f6f\u4ef6\u3002\u76f4\u89c2\u7684\u754c\u9762\u3001\u51fa\u8272\u7684\u8ba1\u7b97\u529f\u80fd\u548c\u56fe\u8868\u5de5\u5177\uff0c\u518d\u52a0\u4e0a\u6210\u529f\u7684\u5e02\u573a\u8425\u9500\uff0c\u4f7fExcel\u6210\u4e3a\u6700\u6d41\u884c\u7684\u4e2a\u4eba\u8ba1\u7b97\u673a\u6570\u636e\u5904\u7406\u8f6f\u4ef6\u3002
Office\u6700\u521d\u662f\u4e00\u4e2a\u63a8\u5e7f\u540d\u79f0\uff0c\u51fa\u73b0\u4e8e1990\u5e74\u4ee3\u65e9\u671f\uff0c\u6307\u4e00\u4e9b\u4ee5\u524d\u66fe\u5355\u72ec\u53d1\u552e\u7684\u8f6f\u4ef6\u7684\u5408\u96c6\u3002\u5f53\u65f6\u4e3b\u8981\u7684\u63a8\u5e7f\u91cd\u70b9\u662f\uff1a\u8d2d\u4e70\u5408\u96c6\u6bd4\u5355\u72ec\u8d2d\u4e70\u8981\u7701\u5f88\u591a\u94b1\u3002\u6700\u521d\u7684Office\u7248\u672c\u5305\u542bWord\u3001Excel\u548cPowerPoint\u3002
\u4e00\u3001\u8fd9\u53ef\u4ee5\u7528DATEDIF\u51fd\u6570\u6765\u5b8c\u6210\u3002
\u4e8c\u3001DATEDIF\u51fd\u6570\u7684\u5b9a\u4e49
DATEDIF\u51fd\u6570\u662fExcel\u9690\u85cf\u51fd\u6570\uff0c\u5728\u5e2e\u52a9\u548c\u63d2\u5165\u516c\u5f0f\u91cc\u9762\u6ca1\u6709\u3002 \u8fd4\u56de\u4e24\u4e2a\u65e5\u671f\u4e4b\u95f4\u7684\u5e74\\u6708\\u65e5\u95f4\u9694\u6570\u3002\u5e38\u4f7f\u7528DATEDIF\u51fd\u6570\u8ba1\u7b97\u4e24\u65e5\u671f\u4e4b\u5dee\u3002
\u8bed\u6cd5
DATEDIF(start_date,end_date,unit)
Start_date \u4e3a\u4e00\u4e2a\u65e5\u671f\uff0c\u5b83\u4ee3\u8868\u65f6\u95f4\u6bb5\u5185\u7684\u7b2c\u4e00\u4e2a\u65e5\u671f\u6216\u8d77\u59cb\u65e5\u671f\u3002
End_date \u4e3a\u4e00\u4e2a\u65e5\u671f\uff0c\u5b83\u4ee3\u8868\u65f6\u95f4\u6bb5\u5185\u7684\u6700\u540e\u4e00\u4e2a\u65e5\u671f\u6216\u7ed3\u675f\u65e5\u671f\u3002
Unit \u4e3a\u6240\u9700\u4fe1\u606f\u7684\u8fd4\u56de\u7c7b\u578b\u3002
Unit \u8fd4\u56de
\u6ce8\uff1a\u7ed3\u675f\u65e5\u671f\u5fc5\u987b\u5927\u4e8e\u8d77\u59cb\u65e5\u671f
\u4e0b\u9762\u4e3e\u4e2a\u5c0f\u4f8b\u5b50\uff1a\u5728\u65e5\u5e38\u5de5\u4f5c\u4e2d\u975e\u5e38\u5b9e\u7528\u3002
\u5047\u5982A1\u5355\u5143\u683c\u5199\u7684\u4e5f\u662f\u4e00\u4e2a\u65e5\u671f\uff0c\u90a3\u4e48\u4e0b\u9762\u7684\u4e09\u4e2a\u516c\u5f0f\u53ef\u4ee5\u8ba1\u7b97\u51faA1\u5355\u5143\u683c\u7684\u65e5\u671f\u548c\u4eca\u5929\u7684\u65f6\u95f4\u5dee\uff0c\u5206\u522b\u662f\u5e74\u6570\u5dee\uff0c\u6708\u6570\u5dee\uff0c\u5929\u6570\u5dee\u3002
\u6ce8\u610f\u4e0b\u9762\u516c\u5f0f\u4e2d\u7684\u5f15\u53f7\u548c\u9017\u53f7\u62ec\u53f7\u90fd\u662f\u5728\u82f1\u6587\u72b6\u6001\u4e0b\u8f93\u5165\u7684\u3002
=DATEDIF(A1,TODAY(),"Y") \u8ba1\u7b97\u5e74\u6570\u5dee
=DATEDIF(A1,TODAY(),"M") \u8ba1\u7b97\u6708\u6570\u5dee
=DATEDIF(A1,TODAY(),"D") \u8ba1\u7b97\u5929\u6570\u5dee
"Y" \u65f6\u95f4\u6bb5\u4e2d\u7684\u6574\u5e74\u6570\u3002
"M" \u65f6\u95f4\u6bb5\u4e2d\u7684\u6574\u6708\u6570\u3002
"D" \u65f6\u95f4\u6bb5\u4e2d\u7684\u5929\u6570\u3002
"MD" start_date \u4e0e end_date \u65e5\u671f\u4e2d\u5929\u6570\u7684\u5dee\u3002\u5ffd\u7565\u65e5\u671f\u4e2d\u7684\u6708\u548c\u5e74\u3002
"YM" start_date \u4e0eend_date \u65e5\u671f\u4e2d\u6708\u6570\u7684\u5dee\u3002\u5ffd\u7565\u65e5\u671f\u4e2d\u7684\u5e74\u3002
"YD" start_date \u4e0e end_date \u65e5\u671f\u4e2d\u5929\u6570\u7684\u5dee\u3002\u5ffd\u7565\u65e5\u671f\u4e2d\u7684\u5e74\u3002
\u4e09\u3001\u5b9e\u4f8b\u8bf4\u660e
\u5b9e\u4f8b1\uff1a
\u9898\u76ee\uff1a \u8ba1\u7b97\u51fa\u751f\u65e5\u671f\u4e3a1973-4-1\u4eba\u7684\u5e74\u9f84
\u516c\u5f0f\uff1a =DATEDIF("1973-4-1",TODAY(),"Y")
\u7ed3\u679c\uff1a 33
\u7b80\u8981\u8bf4\u660e \u5f53\u5355\u4f4d\u4ee3\u7801\u4e3a"Y"\u65f6,\u8ba1\u7b97\u7ed3\u679c\u662f\u4e24\u4e2a\u65e5\u671f\u95f4\u9694\u7684\u5e74\u6570.
\u5b9e\u4f8b2\uff1a
\u9898\u76ee\uff1a \u8ba1\u7b97\u65e5\u671f\u4e3a1973-4-1\u548c\u5f53\u524d\u65e5\u671f\u7684\u95f4\u9694\u6708\u4efd\u6570.
\u516c\u5f0f\uff1a =DATEDIF("1973-4-1",TODAY(),"M")
\u7ed3\u679c\uff1a 403
\u7b80\u8981\u8bf4\u660e \u5f53\u5355\u4f4d\u4ee3\u7801\u4e3a"M"\u65f6,\u8ba1\u7b97\u7ed3\u679c\u662f\u4e24\u4e2a\u65e5\u671f\u95f4\u9694\u7684\u6708\u4efd\u6570.
\u5b9e\u4f8b3\uff1a
\u9898\u76ee\uff1a \u8ba1\u7b97\u65e5\u671f\u4e3a1973-4-1\u548c\u5f53\u524d\u65e5\u671f\u7684\u95f4\u9694\u5929\u6570.
\u516c\u5f0f\uff1a =DATEDIF("1973-4-1",TODAY(),"D")
\u7ed3\u679c\uff1a 12273\u7b80\u8981\u8bf4\u660e \u5f53\u5355\u4f4d\u4ee3\u7801\u4e3a"D"\u65f6,\u8ba1\u7b97\u7ed3\u679c\u662f\u4e24\u4e2a\u65e5\u671f\u95f4\u9694\u7684\u5929\u6570.
\u5b9e\u4f8b4\uff1a
\u9898\u76ee\uff1a \u8ba1\u7b97\u65e5\u671f\u4e3a1973-4-1\u548c\u5f53\u524d\u65e5\u671f\u7684\u4e0d\u8ba1\u5e74\u6570\u7684\u95f4\u9694\u5929\u6570.
\u516c\u5f0f\uff1a =DATEDIF("1973-4-1",TODAY(),"YD")
\u7ed3\u679c\uff1a 220
\u7b80\u8981\u8bf4\u660e \u5f53\u5355\u4f4d\u4ee3\u7801\u4e3a"YD"\u65f6,\u8ba1\u7b97\u7ed3\u679c\u662f\u4e24\u4e2a\u65e5\u671f\u95f4\u9694\u7684\u5929\u6570.\u5ffd\u7565\u5e74\u6570\u5dee
\u5b9e\u4f8b5\uff1a
\u9898\u76ee\uff1a \u8ba1\u7b97\u65e5\u671f\u4e3a1973-4-1\u548c\u5f53\u524d\u65e5\u671f\u7684\u4e0d\u8ba1\u6708\u4efd\u548c\u5e74\u4efd\u7684\u95f4\u9694\u5929\u6570.
\u516c\u5f0f\uff1a =DATEDIF("1973-4-1",TODAY(),"MD")
\u7ed3\u679c\uff1a 6
\u7b80\u8981\u8bf4\u660e \u5f53\u5355\u4f4d\u4ee3\u7801\u4e3a"MD"\u65f6,\u8ba1\u7b97\u7ed3\u679c\u662f\u4e24\u4e2a\u65e5\u671f\u95f4\u9694\u7684\u5929\u6570.\u5ffd\u7565\u5e74\u6570\u548c\u6708\u4efd\u4e4b\u5dee
\u5b9e\u4f8b6\uff1a
\u9898\u76ee\uff1a \u8ba1\u7b97\u65e5\u671f\u4e3a1973-4-1\u548c\u5f53\u524d\u65e5\u671f\u7684\u4e0d\u8ba1\u5e74\u4efd\u7684\u95f4\u9694\u6708\u4efd\u6570.
\u516c\u5f0f\uff1a =DATEDIF("1973-4-1",TODAY(),"YM")
\u7ed3\u679c\uff1a 7
\u7b80\u8981\u8bf4\u660e \u5f53\u5355\u4f4d\u4ee3\u7801\u4e3a"YM"\u65f6,\u8ba1\u7b97\u7ed3\u679c\u662f\u4e24\u4e2a\u65e5\u671f\u95f4\u9694\u7684\u6708\u4efd\u6570.\u4e0d\u8ba1\u76f8\u5dee\u5e74\u6570\u6ce8\u610f\uff1aTODAY()\u51fd\u6570\u83b7\u53d6\u7684\u662f\u7cfb\u7edf\u5f53\u524d\u65e5\u671f\uff0c\u6240\u6709\u5217\u4e3e\u7684\u5b9e\u4f8b\u4e3a2006-11-7\u65e5\u7684\u8ba1\u7b97\u7ed3\u679c\uff0c\u5e76\u4e0d\u4e00\u5b9a\u548c\u4f60\u5f97\u5230\u7684\u7ed3\u679c\u76f8\u7b26\u3002
\u56db\u3001\u5047\u8bbe\u5f00\u59cb\u65e5\u671f\u5199\u5728A1\u5355\u5143\u683c\uff0c\u7ed3\u675f\u65e5\u671f\u5199\u5728B1\u5355\u5143\u683c\uff0c\u8981\u5728C1\u5355\u5143\u683c\u5f97\u51fa\u4e24\u4e2a\u65e5\u671f\u76f8\u5dee\u7684\u5e74\u6570\u548c\u6708\u6570\uff0c\u5219\u516c\u5f0f\u53ef\u4ee5\u5199\u6210\u5982\u4e0b
=DATEDIF(A1,B1,"y")&"\u5e74"&DATEDIF(A1,B1,"ym")&"\u4e2a\u6708"
因为不知道你说的两个日期间存在的月份数，包不包括当月的，就有两种情况
一、不包括起始日期和结束日期这两个月的月份数（即两个日期间相隔的月份数）
在B3单元格输入公式
二、包括的话
注意：单元格格式设为
=DATEDIF(起始日期,到期日期,"m")
因为我的区间是2014年1月1日，也就是需要比较起始日的大小
=DATEDIF(IF(起始日期单元格>--"2014-1-1",起始日期单元格,"2014-1-1"),IF(到期日期单元格>--"2014-6-30","2014-6-30",到期日期单元格),"m")
或
=DATEDIF(MAX(起始日期单元格,"2014-1-1"),MIN(到期日期单元格,"2014-6-30"),"m")
=if(日期1>日期2,month(日期1)-month(日期2),month(日期2)-month(日期1))
就这样子，比较两个日期的大小，用大日期的月份减去小日期的月份
max(datedif(max(--"2014-1-1",A1),min(--"2014-6-30",B1),"m"), 6)
绛旓細1銆佹瘮濡備粖澶╂槸2019-6-16锛屽湪Excel琛ㄦ牸涓緭鍏ュ叕寮=MONTH(NOW())銆2銆佽緭鍏ュ叕寮忓悗锛屽洖杞﹀氨鍙互寰楀埌褰撳ぉ鐨勬湀浠銆3銆佸鏋滆姹備竴涓棩鏈熺殑鏈堜唤锛岃緭鍏ュ叕寮= month(G11) 銆4銆佽緭鍏ュ叕寮忓悗锛屾寜鍥炶溅閿氨鍙互寰楀埌鍓嶉潰鏃ユ湡鐨勬湀浠芥暟銆5銆侀変腑鍏紡鍗曞厓鏍硷紝鐒跺悗涓嬫媺澶嶅埗鍏紡灏卞彲浠ヤ簡銆侲xcel涓绠楁湀浠界殑鍏紡鏄細MONTH(...
绛旓細棣栧厛锛屽惎鍔‥xcel锛屾墦寮浣犵殑宸ヤ綔琛紝纭繚鏃ユ湡鏁版嵁瀛樺偍鍦ˋ鍒椾腑銆備负浜嗕究浜庤绠楋紝浣犻渶瑕佸皢鏃ユ湡鏍煎紡杞崲涓烘暟瀛銆傚彲浠ラ氳繃蹇嵎閿"Ctrl+H"鎵撳紑鏇挎崲鍔熻兘锛岃緭鍏ユ棩鏈熸牸寮忓"骞-鏈-鏃"锛岀劧鍚庢浛鎹负"0000-00-00"杩欐牱鐨勬暟瀛楁牸寮忋傚畬鎴愭浛鎹㈠悗锛孉鍒楃殑鏃ユ湡浼氭樉绀轰负鏁板笺傛帴涓嬫潵锛屽湪B1鍗曞厓鏍间腑锛岃緭鍏ュ叕寮"=MID(A1,6,...
绛旓細鍦Excel涓紝褰撲綘闇瑕佹牴鎹緭鍏ョ殑骞存湀鏉璁＄畻绱鏈堜唤鏃讹紝鍙互鍊熷姪鍐呯疆鐨勬棩鏈熷嚱鏁般傞鍏堬紝浜嗚В濡備綍浠庢棩鏈熶腑鎻愬彇骞淬佹湀淇℃伅鏄叧閿備娇鐢ㄥ嚱鏁癕ONTH(serial_number)鍜孻EAR(serial_number)鑳藉鍒嗗埆鑾峰彇鎸囧畾鏃ユ湡鐨勬湀浠藉拰骞翠唤锛屽叾涓璼erial_number鍙互鏄洿鎺ョ殑鏃ユ湡鏂囨湰銆佸崟鍏冩牸寮曠敤锛屾垨鑰呭叾浠栧叕寮忔垨鍑芥暟鐨勭粨鏋溿備緥濡傦紝濡傛灉...
绛旓細棣栧厛锛鎵惧埌 鏃ユ湡 鎵鍦ㄥ垪鍜 鏈堜唤 鎵鍦ㄥ垪銆傚叾娆★紝榧犳爣鐐瑰嚮鏈堜唤鍒楃涓涓崟鍏冩牸锛屾垜浠湪缂栬緫鏍忎腑杈撳叆鍑芥暟"=MONTH()"銆傦紙娉細鎷彿鏄嫳鏂囩姸鎬佷笅鐨勩傦級鐒跺悗锛屽皢鍏夋爣鏀惧埌鍒氬垰杈撶殑鎷彿褰撲腑鍘汇傜劧鍚庣偣鍑绘棩鏈熷垪涓庢湀浠藉垪鐩稿搴旂殑鍗曞厓鏍笺傛瘮濡傦細鎴戜滑杈撳嚱鏁版椂鐐瑰嚮鐨勬湀浠藉垪鍗曞厓鏍兼槸B鍒楃2琛岋紝鎵浠ョ幇鍦ㄦ垜浠渶瑕佺偣鍑荤殑...
绛旓細鍦‥xcel涓紝濡傛灉浣犳兂璁＄畻涓や釜鏃ユ湡涔嬮棿鐨勬湀浠藉樊寮傦紝鍙互浣跨敤DATEDIF鍑芥暟锛岃繖鏄竴涓殣钘忎絾鍔熻兘寮哄ぇ鐨勫伐鍏凤紝铏界劧鍦ㄥ府鍔╂枃妗ｅ拰鍑芥暟鍒楄〃涓彲鑳戒笉鏄撳療瑙夈傝繖涓嚱鏁扮殑鏍煎紡濡備笅锛=DATEDIF(璧峰鏃ユ湡, 缁撴潫鏃ユ湡, 闂撮殧绫诲瀷)鍏朵腑锛岀涓変釜鍙傛暟"闂撮殧绫诲瀷"鏈夊叚绉嶅彲鑳界殑閫夐」锛"Y" 琛ㄧず鏃堕棿娈典腑鐨勫畬鏁村勾鏁般"M" 琛ㄧず...
绛旓細1銆佹墦寮excel琛ㄦ牸锛屽湪A鍒椾腑杈撳叆鏃ユ湡銆2銆侀渶瑕佸皢鏃ユ湡杞崲涓烘暟瀛楁牸寮忥紝浣跨敤鈥淐trl+H鈥濊皟鍑烘浛鎹紝鎸夌収涓嬪浘杈撳叆鏉′欢杩涜鏇挎崲鎿嶄綔銆3銆佷笅鍥句负鏇挎崲鎴愭暟瀛楁牸寮忕殑鏃ユ湡銆4銆佸湪B1鍗曞厓鏍间腑杈撳叆鍏紡锛==MID(A1,6,1)锛屾剰鎬濅负鍙朅1鍗曞厓鏍间腑绗叚涓暟瀛椼5銆佸皢鎵鏈夋棩鏈熷唴鐨鏈堜唤鏁板瓧鍙栧嚭銆6銆佺劧鍚庡湪C1鍗曞厓鏍煎唴杈撳叆...
绛旓細姝ラ涓锛氬惎鍔ㄤ綘鐨勭數鑴戯紝鎵惧埌骞跺弻鍑绘柊寤轰竴涓Excel宸ヤ綔琛紝杩欏皢涓轰綘鎻愪緵涓涓┖鐧界殑宸ヤ綔鐜銆傛楠や簩锛氭墦寮鏂板伐浣滆〃鍚庯紝涓轰簡灞曠ず璁＄畻杩囩▼锛屾垜浠湪绌虹櫧鍖哄煙杈撳叆浣犳兂瑕佽绠楄窛绂荤殑鏃ユ湡锛屼緥濡傦細1999骞3鏈1鏃ャ傛楠や笁锛氭帴涓嬫潵锛屽叧閿楠ゆ潵浜嗐傚湪浣犲垰鎵嶈緭鍏ョ殑鏃ユ湡鏃佽竟锛岃緭鍏xcel鐨凞ATEDIF鍑芥暟锛屾牸寮忎负=DATEDIF("...
绛旓細涓銆佺‘瀹氬綋鍓嶆棩鏈 浣跨敤绯荤粺鍑芥暟锛氬ぇ澶氭暟鎿嶄綔绯荤粺鍜岀紪绋嬭瑷閮芥彁渚涗簡鑾峰彇褰撳墠鏃ユ湡鐨勫嚱鏁般備緥濡傦紝鍦Excel涓紝鍙互浣跨敤TODAY()鎴朜OW()鍑芥暟鏉ヨ幏鍙栧綋鍓嶆棩鏈熷拰鏃堕棿銆備簩銆璁＄畻鏃ユ湡涔嬮棿鐨勫樊鍊 浣跨敤DATEDIF鍑芥暟锛圗xcel锛夛細DATEDIF鍑芥暟鏄疎xcel涓殑涓涓殣钘忓嚱鏁帮紝鐢ㄤ簬璁＄畻涓や釜鏃ユ湡涔嬮棿鐨勫勾鏁般鏈堟暟鎴栧ぉ鏁般傜洿鎺ョ浉鍑忥細鍦...
绛旓細excel璁＄畻鍦ㄨ亴鏈堜唤鏂瑰紡濡備笅锛1銆佹墦寮excel琛ㄦ牸锛2銆侀夋嫨鍗曞厓鏍艰緭鍏=DATEDIF(A2,B2,"M")锛孉2涓哄紑濮嬫椂闂达紝B2涓虹粨鏉熸椂闂达紝M鏄璁＄畻鏈堟暟鐨勫弬鏁般傜ず鎰忓浘锛氥愬姞鍏ョぞ缇や竴璧峰涔犮戞兂瑕佷簡瑙ｆ洿澶氬叧浜巈xcel鐨勭浉鍏充俊鎭紝鎺ㄨ崘鍜ㄨ绉掑彲鑱屽満銆傜鍙亴鍦轰互浜х爺涓庤鐮斾负鏍稿績绔炰簤鍔涳紝缁煎悎杩愮敤浜哄伐鏅鸿兘銆佸ぇ鏁版嵁銆佷簯璁＄畻绛夋柊...
绛旓細鍦Excel涓紝瑕佸揩閫熺粺璁″～鍏ユ棩鏈熺殑鍚鏈堜唤鏁伴噺锛屽彲浠ユ寜鐓т互涓嬫楠ゆ搷浣溿傞鍏堬紝鎵撳紑浣犵殑Excel琛ㄦ牸锛岀‘淇濇棩鏈熸暟鎹凡褰曞叆A鍒椼傛帴鐫锛屽皢杩欎簺鏃ユ湡杞崲涓烘暟瀛楁牸寮忥紝鏂规硶鏄湪"Ctrl+H"蹇嵎閿悗杈撳叆鐗瑰畾鐨勬浛鎹㈡潯浠讹紝浠ヤ究鍘婚櫎鏃ユ湡鏍煎紡锛岀暀涓嬫暟瀛椼傚畬鎴愭浛鎹㈠悗锛孉鍒楃殑鏃ユ湡浼氬憟鐜板嚭鏁板瓧褰㈠紡銆傜劧鍚庯紝杩涘叆B1鍗曞厓鏍硷紝杈撳叆鍏紡...
