excel 表格日期计算问题,将日期之间计算相差多少天,例:2011.11.01-2012.02.30之间相差多少天,或者将两
\u5982\u4f55\u7528EXCEL\u8ba1\u7b97\u4e24\u4e2a\u65e5\u671f\u4e4b\u95f4\u76f8\u5dee\u7684\u5e74\u6570\u548c\u6708\u6570\uff1f\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"
A1\u4e3a2011-3-30\uff0cB1\u4e3a2012-1-12\uff0c\u5728C1\u8f93\u5165=B1-A1 \u5373\u4e3a\u4e4b\u95f4\u5929\u6570\u3002
一、将它们从“-”这个符号之间分开:
1、数据→分列→下一步,在分隔符号的其他那里打勾,并填上“- ”这个符号;
2、下一步→完成;
3、这个时候就被分成两列了,然后按ctrl+F,点替换,将“.”这个符号替换成“-”这个符号,这样所有的都会变成日期格式了。
二、用公式将其计算:
假如这两个日期分列完了之后分别在A列和B列,然后在C1单元格输入公式=B1-A1,就好了。
三、2月没有30号……
=datedif(2011.11.01,2012.02.30,"d")
=DATEDIF("2011-11-1",“2012-02-30”),"D")
扩展阅读:excel 日期 周期计算 ... excel计算合同到期日 ... excel自动计算月份数 ... excel日期自动生成年月日 ... excel 计算日期差值 ... excel 中的日期公式 ... excel 日期计算农历公式 ... excel时间计算公式 分钟 ... excel 日期自动计算天数 ...