excel中如何进行时间比较,比如现在有个案例.出库时间:2013-06-03 21:55:00,此时间与当日24点进行比较,
excel\u8868\u683c\u8f93\u5165\u65f6\u95f424\u70b9\u4ee5\u540e\u4e0d\u53d8\u4e3a0\u70b9\u5728\u5408\u6cd5\u7684EXCEL\u6570\u636e\u7c7b\u578b\u4e2d\uff0c\u65f6\u95f4\u6570\u636e\u662f\u6ca1\u670924:00\u7684
\u56e0\u4e3a\u8fd9\u6837\u7684\u8bdd\uff0c0:00\u548c24:00\u4f1a\u4ea7\u751f\u6b67\u4e49
\u9664\u975e\u662f\u7528\u6587\u672c\u5f62\u5f0f\u663e\u793a\u8d85\u8fc724:00\u7684\u60c5\u51b5
\u5426\u5219\u662f\u4e0d\u53ef\u80fd\u7684
\u81f3\u4e8e\u8ba1\u7b97\u4e0d\u662f\u540c\u4e00\u5929\u7684\u65f6\u95f4\u5dee
\u7b80\u5355\u7684\u529e\u6cd5\u662f\u7528MOD\u51fd\u6570
\u6bd4\u5982\uff1a
A1\u4e3a8:00
B1\u4e3a24:00 \uff08\u4f46\u662f\u663e\u793a\u4e3a0:00\uff09
\u65f6\u95f4\u5dee\u516c\u5f0f\u4e3a\uff1a=MOD(B1-A1, 24)
\u8fd9\u6837\u7684\u7ed3\u679c\u662f16
\u4f46\u8fd9\u53ea\u80fd\u8ba1\u7b9724\u5c0f\u65f6\u4ee5\u5185\u7684\u5dee\u503c
\u6700\u6b63\u89c4\u7684\u65b9\u6cd5\u9700\u8981\u8fde\u540c\u65e5\u671f\u4e00\u8d77\u8ba1\u7b97
\u6bd4\u5982\uff1a
A1\u4e3a2013-1-8 8:00
B1\u4e3a2013-1-8 24:00 \uff08\u5c06\u4f1a\u663e\u793a\u4e3a\uff1a2013-1-9 0:00\uff09
\u7136\u540e\u540e\uff1a=TEXT(B1-A1, "H:M")
\u53ef\u5f97\u523016:00
\u5982\u679c\u662f\u8d85\u8fc724\u5c0f\u65f6\u4ee5\u4e0a\u7684\u65f6\u95f4\u5dee
\u5219\u516c\u5f0f\u9700\u8981\u52a0\u4e0a\u5929\u7684\u6570\u503c\uff1a=INT(B1-A1) & "\u5929" & TEXT(B1-A1, "H:M")
\u5c06\u5355\u5143\u683c\u8bbe\u7f6e\u4e3a\u65f6\u95f4\u683c\u5f0f\uff0c\u8f93\u5165\u65b9\u5f0f\u4e3a26:0\uff08\u4e2d\u95f4\u7528\u7684\u662f\u5192\u53f7\uff09\uff0c\u5219\u4f1a\u663e\u793a\u4e3a2:00
假设在B1单元格判定OK或者迟到，使用函数：=IF(A1<YEAR(A1)&"-"&MONTH(A1)&"-"&DAY(A1)&" "&"24:00:00","OK","迟到")绛旓細浠诲姟鏄鍒朵綔瀹屾垚浠诲姟涓庤鍒掍换鍔鐨勫姣鍥捐〃锛屼负浜嗘洿濂界殑瀹屾垚鍥炬爣鐨勫埗浣滐紝闇瑕佸仛涓鍒楄緟鍔╁垪锛屽涓嬪浘鏈畬鎴愬垪锛涘湪鏈畬鎴愬垪杈撳叆=鐒跺悗鐐瑰嚮C2鍗曞厓鏍-B2鍗曞厓鏍硷紝杈撳叆瀹屾垚鍚庢暡鍑诲洖杞︼紝涔嬪悗锛屽悜涓嬫嫋鍔ㄥ畬鎴愬叾浠栫被浼奸」鐨勮绠楋紱璁＄畻缁撴灉濡備笅锛涢変腑瑕佸埗浣滃浘琛ㄧ殑鏈畬鎴愬垪銆佸畬鎴愬垪銆佹у悕鍒楋紱鎻掑叆涓涓爢绉煴鐘跺浘 浣滀负杈呭姪...
绛旓細涓句緥锛氾紵鐜板湪A銆丅涓ゅ垪涓緭鍏鏃堕棿锛鎶夾B涓ゅ垪鐨鏍煎紡璁剧疆涓2013-4-30 14:00锛岀劧鍚庣偣鍑籇2鍗曞厓鏍硷紝杈撳叆=B2-A2锛屽洖杞︼紝鐢ㄥ～鍏呮墜鏌勫～鍏呭叾浠栧崟鍏冩牸锛
绛旓細閫氳繃TEXT(NOW(),"h:mm:ss")鑾峰彇绯荤粺鏃堕棿杩涜瀵规瘮 Excel鐗堟湰鍙傝冿細2010 A1鍗曞厓鏍兼槸娴嬭瘯鏃堕棿锛 23:00:00 1銆丅1鍗曞厓鏍艰緭鍏ュ叕寮忥細=a1-TEXT(NOW(),"h:mm:ss")2銆佸洖杞︼紝鏌ョ湅鏁堟灉
绛旓細璇烽棶濡備綍鍦‥XCEL涓瘮杈涓や釜鏃堕棿鐐圭殑澶у皬鍋囪鏃堕棿鍦ˋ1涓紝鍒欏叕寮忎负锛=IF(A1="","",IF(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))<TIME(8,,),"OK","杩熷埌"))鎴栬呯畝鍖栦负锛=IF(A1="","",IF(HOUR(A1)<8,"OK","杩熷埌"))鎺㈢┒鐨勪竴鑸繃绋嬫槸浠庡彂鐜伴棶棰樸佹彁鍑洪棶棰樺紑濮嬬殑,鍙戠幇闂鍚,鏍规嵁鑷繁宸叉湁...
绛旓細鍦―1涓緭鍏ユ垨澶嶅埗绮樿创涓嬪垪鍏紡涔嬩竴 =IF(C1<=A1-"00:10",B1+"00:20",C1+B1-A1+"00:30")=IF(C1<=A1-TIME(,10,),B1+TIME(,20,),C1+B1-A1+TIME(,30,))=IF(C1<=A1-10/60/24,B1+20/60/24,C1+B1-A1+30/60/24)涓嬫媺濉厖 ...
绛旓細Excel璁＄畻涓や釜鏃ユ湡涔嬮棿鐨勬椂闂宸殑鏂规硶 1銆佸涓嬪浘琛ㄦ牸鏁版嵁锛岃鏄濡備綍璁＄畻涓や釜鏃ユ湡闂寸殑鐩稿樊澶╂暟锛2銆佽璁＄畻鐩稿樊澶╂暟姣旇緝绠鍗曪紝鍦ㄧ┖鐧界殑鍗曞厓鏍间腑杈撳叆鈥=B2-A2鈥濓紝鍗崇粨鏉熺殑鏃ユ湡鍑忓幓寮濮嬬殑鏃ユ湡锛涙樉绀虹殑鏁存暟灏辨槸鐩稿樊鐨勫ぉ鏁颁簡锛3銆佺洿鎺ヤ娇鐢ㄢ滅粨鏉熸棩鏈-寮濮嬫棩鏈熲濆湪鏌愪簺鍗曞厓鏍兼牸寮忎笅骞朵笉鑳藉畬鍏ㄥ噯纭紝杩樻湁涓绉...
绛旓細鍋囪A鍒楁槸鍙傚姞宸ヤ綔鏃堕棿锛B鍒楁槸鍏ュ厷鏃堕棿 鍒欏湪C1杈撳叆 =if(A1-B1>=0,"宸ヤ綔鍚庡叆鍏","瀛︿範鏈熼棿鍏ュ厷")
绛旓細1銆侀鍏堟墦寮Excel琛ㄦ牸锛屽苟鍦ㄨ〃鏍煎崟鍏冩牸涓緭鍏ュソ淇℃伅銆2銆侀紶鏍囬変腑銆怌2銆戝崟鍏冩牸锛屽苟杈撳叆鍏紡锛=TEXT(B2-A2,"h灏忔椂m鍒嗛挓s绉")锛屾寜鍥炶溅閿3銆佺劧鍚庨紶鏍囨寚閽堟斁鍦ㄣ怌2銆戝崟鍏冩牸鍙充笅瑙掞紝寰呴紶鏍囨寚閽堝彉鎴愨+鈥濈鍙凤紝鎸変綇榧犳爣宸﹂敭鍚戜笅鎷栧姩銆4銆佹渶鍚庢祻瑙堜笅璁剧疆鐨勬晥鏋滐紝濡備笅鍥炬墍绀鏃堕棿宸氨璁＄畻鍑烘潵浜嗐
绛旓細闄や簡IF鍑芥暟锛岃繕鍙互浣跨敤鏉′欢鏍煎紡涓鐨勬椂闂娈垫牸寮忔潵瀹炵幇蹇熷垎鏃╀腑鏅氥傞変腑闇瑕佸鐞嗙殑鏃堕棿鍒楋紝鐐瑰嚮鏉′欢鏍煎紡锛屽苟閫夋嫨鏃堕棿娈垫牸寮忥紝鑷姩璇嗗埆鏃堕棿骞舵寜鐓ф棭涓櫄涓変釜鏃堕棿娈垫牸寮忓寲琛ㄦ牸銆傝繖绉嶆柟寮忔洿鍔犵畝渚匡紝閫傚悎鍗曠嫭澶勭悊鏃堕棿娈电殑鍗曞厓鏍笺傚洖绛斿涓嬶細1. 鍦‥xcel琛ㄦ牸涓紝閫変腑闇瑕佸垎鏃╀腑鏅氱殑鏃堕棿鍒椼2. 鍦ㄢ滃紑濮嬧濋夐」鍗...
绛旓細鍋囪鍦˙1鍗曞厓鏍煎垽瀹歄K鎴栬呰繜鍒帮紝浣跨敤鍑芥暟锛=IF(A1<YEAR(A1)&"-"&MONTH(A1)&"-"&DAY(A1)&" "&"24:00:00","OK","杩熷埌")
