用excel计算两个时间的差值，单位是天，计算结果中的小时如果大于8小时按照一天计算，谁给我个公式啊
\u5728\u7535\u5b50\u8868\u683c\u4e2d\u5982\u4f55\u7528\u516c\u5f0f\u8ba1\u7b97\u4e24\u4e2a\u65f6\u95f4\u4e4b\u95f4\u7684\u65f6\u95f4\u5dee\u5047\u8bbeA1=07\uff1a30 B1 = 18\uff1a00 \u7ed3\u679c\u8981\u50a8\u5b58\u5728C1\u4e2d\uff1a
\u90a3\u4e48\uff0cC1\u5185\u5bb9\u4e3a\uff1a
=IF(OR($A1="",$B1=""),"",SUM(HOUR(SUM($B1,-$A1)),MINUTE(SUM($B1,-$A1))/60))
\u6ce8\uff1ac1\u683c\u5f0f\u8981\u8bbe\u7f6e\u4e3a\u5e38\u89c4
____________________________________________
\u53d1\u73b0\u4e00\u4e2a\u66f4\u7b80\u5355\u7684\u65b9\u6cd5\uff1a
=IF(OR($A1="",$B1=""),"",24*($B1-$1))
\u6ce8\uff1ac1\u683c\u5f0f\u8981\u8bbe\u7f6e\u4e3a\u5e38\u89c4
\u4ee5\u5206\u949f\u663e\u793a\uff1a
=text(E2-D2,"[m]")
\u4ee5\u5c0f\u65f6\u663e\u793a\uff1a
=text(E2-D2,"[h]")
\u4ee5\u5206\u949f\u663e\u793a\uff1a
=text(E2-D2,"[m]")
\u4ee5HH:MM\u663e\u793a\uff1a
=text(E2-D2,"HH:MM")
在A3输入公式如下
=IF(((A2-A1)-INT(A2-A1))*24>=8,INT(A2-A1)+1,INT(A2-A1))
应该是35天多一些，四舍五入是36天，先分列，把前面和后面分开，即A1和B1，
把C1设成常规格式，直接输入=B1-A1，回车，得到35.59305556，C1输入公式
=ROUND(C1,0)，回车得到36，是不是这个问题，请回答，试试看
=CEILING(B1-A1+2/3,1)
=INT(B1-A1)+IF(HOUR(B1-A1)>=8,1,0)
绛旓細鍦‥xcel涓紝瑕佽绠椾袱涓椂闂寸殑宸硷紝鍙互浣跨敤"鍑忔硶"杩愮畻绗︽潵瀹炵幇銆傞鍏堬紝纭繚涓や釜鏃堕棿鐨勬牸寮忔纭紝渚嬪灏嗗畠浠墜鍔ㄨ緭鍏ヤ负鏃堕棿鏍煎紡(HH:MM:SS)鎴栦娇鐢ㄦ椂闂村嚱鏁板皢鍏惰浆鍖栦负鏃堕棿鏍煎紡銆傜劧鍚庯紝灏嗚鍑忓幓鐨勬椂闂存斁鍦ㄥ噺娉曡繍绠楃鐨勫乏杈癸紝琚噺鍘荤殑鏃堕棿鏀惧湪鍙宠竟锛岀敤鍑忓彿杩炴帴瀹冧滑銆傛渶鍚庯紝鎸変笅"Enter"閿紝Excel灏变細璁＄畻鍑轰袱...
绛旓細1銆佸垱寤轰竴涓┖鐧界殑excel鏂囨。 2銆佸湪excel涓濉啓3鍒楁紨绀烘暟鎹紝鍏朵腑锛屾棩鏈1銆佹棩鏈2鏄袱鍒楁棩鏈熸牸寮忕殑鏁版嵁銆傛湰鏂囦粎鍋氭紨绀猴紝鎵浠ワ紝鏁版嵁琛屽苟涓嶅 3銆佸湪excel涓紝鏂板紑涓鍒楋紝鍋囪鍙仛鈥滄棩鏈熷樊鈥濆悕绉帮紝鍊=鏃ユ湡1鏍忎綅 - 鏃ユ湡2鏍忎綅銆傝緭鍏ュ畬鎴愬悗鍥炶溅銆4銆佽繖涓椂鍊欐棩鏈熷樊鏍忎綅绗竴涓硷紙甯﹀叕寮忕殑鍗曞厓鏍硷級鍙充笅瑙...
绛旓細1銆乨atedif鍑芥暟鏄痚xcel鐨勯殣钘忓嚱鏁帮紝涓昏鐢ㄤ簬璁＄畻鏃ユ湡涔嬪樊锛屼笉鏄洓鑸嶄簲鍏ワ紝鏄悜涓嬪彇鏁銆2銆佷互涓嬪浘涓轰緥锛屽湪C2鍗曞厓鏍艰緭鍏=DATEDIF(A1,A2,y)锛屽叾涓瓂琛ㄧず璁＄畻骞翠唤涔嬪樊銆3銆佸悓鏍峰湪C4杈撳叆=DATEDIF(A1,A2,m),鍏朵腑m琛ㄧず璁＄畻鏈堜唤涔嬪樊銆4銆佸悓鏍峰湪C4杈撳叆=DATEDIF(A1,A2,m),鍏朵腑m琛ㄧず璁＄畻澶╂暟涔嬪樊銆傝繕鍙互鐩...
绛旓細1銆侀鍏堝湪excel琛ㄦ牸涓緭鍏ヤ袱缁勬棩鏈熸暟鎹紝闇瑕佽绠椾袱缁勬棩鏈熺殑鏃ユ湡宸銆2銆佸湪C1鍗曞厓鏍间腑鐩存帴杈撳叆璁＄畻鍏紡锛=B1-A1銆3銆佺偣鍑诲洖杞︽寜閿紝鍗冲彲灏嗚緭鍏ョ殑鍏紡鐢熸垚璁＄畻缁撴灉锛屽彲浠ョ湅鍒版鏃惰绠楃殑鏃ユ湡宸间负鈥37澶┾濄4銆佸啀鍦ㄨ〃鏍间腑杈撳叆涓ょ粍鏃堕棿鏁版嵁锛岄渶瑕佽绠楁椂闂村樊鍊笺5銆佸湪C3鍗曞厓鏍煎唴杈撳叆璁＄畻鍏紡锛=B3-A3銆6...
绛旓細Excel琛ㄦ牸涓や釜鏃ユ湡涔嬮棿鐨勬椂闂村樊璁＄畻鏂规硶锛1銆佹墦寮Excel骞舵墦寮闇瑕璁＄畻涓や釜鏃ユ湡涔嬮棿鐨骞淬佹湀銆佹棩宸殑琛ㄦ牸 2銆侀鍏堟垜浠绠楁棩鏈熷拰鏃ユ湡涔嬮棿鐨勫勾浠斤紝鐐瑰嚮涓涓┖鐧藉崟鍏冩牸锛岃緭鍏ュ嚱鏁癧=DATEDIF(寮濮嬫棩鏈燂紝缁撴潫鏃ユ湡锛屸滄瘮杈冨崟浣峐鈥)]锛屾瘮杈冨崟浣嶄负骞(Y)锛屾湀(M)锛屾棩(D);渚嬪锛孾=DATEDIF(A2,B2锛"Y")]锛...
绛旓細濡傛灉闇瑕璁＄畻杈冧负澶嶆潅鐨勬椂闂村樊锛屽彲浠ョ粨鍚堜娇鐢ㄥ涓嚱鏁版潵瀹炵幇銆傚湪Excel涓鍋囪鍦ㄥ崟鍏冩牸a1鍜宎2鍗曞厓鏍间腑鏈変袱涓棩鏈熸牸寮忕殑涓や釜鏃堕棿鏁版嵁銆傝绠楄繖涓や釜鍗曞厓鏍肩殑鏃堕棿宸彲浠ョ敤a2-a1锛岃繖鏍疯绠楀嚭鏉ョ殑缁撴灉鏄繖涓や釜鏃ユ湡鐩稿樊鐨勫ぉ鏁般傝嫢鎯宠绠楄繖涓や釜鏃ユ湡鐩稿樊鐨勫勾鏁板彲浠ュ皢宸鍐嶉櫎浠365銆傚嵆(a2-a1)/365銆備竴銆佹棩鏈熻绠...
绛旓細1銆侀鍏堝湪鎵撳紑鐨excel琛ㄦ牸涓緭鍏ヤ竴缁勬棩鏈熸暟鎹紝闇瑕佸湪璇ョ粍鏃ユ湡涓婂鍔犳垨鍑忓皯澶╂暟銆2銆佷互澧炲姞澶╂暟涓轰緥锛屽湪B1鍗曞厓鏍间腑杈撳叆璁＄畻鍏紡锛=A1+45锛屾剰鎬濇槸鍦ˋ1鐨勬棩鏈熷熀纭涓婂鍔45澶┿3銆佺偣鍑诲洖杞﹀嵆鍙敓鎴愯绠楁暟鎹紝鍙互鐪嬪埌姝ゆ椂B1鍗曞厓鏍间腑鏄剧ず鐨勬棩鏈熶负澧炲姞浜45澶╀箣鍚庣殑鏃ユ湡銆4銆佸鏋滈渶瑕佹壒閲忓鍔犳棩鏈燂紝鍙互灏咮1...
绛旓細1銆佺數鑴戞墦寮瑕璁＄畻鏃堕棿宸殑Excel琛ㄦ牸銆2銆佹墦寮Excel琛ㄦ牸鍚庯紝杈撳叆鍏紡=TEXT(E3-D3,"[h]:mm")銆3銆佽緭鍏ュ叕寮忓悗锛屽洖杞﹀氨寰楀埌涓や釜鏃堕棿鐨宸簡銆4銆佷笅鎷夊鍒跺叕寮忥紝灏卞彲浠ユ眰鍑烘墍鏈夌殑鏃堕棿宸簡銆
绛旓細鈥浣跨敤excel鍑芥暟璁＄畻涓や釜鏃ユ湡鍙傛暟鐨勫樊鍊鈥濈殑鎿嶄綔姝ラ鏄細1銆佷互Excel 2016涓轰緥锛屾墦寮Excel宸ヤ綔琛;2銆佺敱宸茬煡鏉′欢鍙煡锛岄渶瑕佹牴鎹瓵鍒楃殑寮濮嬫棩鏈熷拰B鍒楃殑缁撴潫鏃ユ湡锛岃绠椾箣闂撮棿闅旂殑澶╂暟锛屽彲閫氳繃DATEDIF鍑芥暟姹傝捣姝㈡棩鏈熺殑闂撮殧澶╂暟锛屼篃鍙洿鎺ラ氳繃缁撴潫鏃ユ湡鍑忓幓寮濮嬫棩鏈燂紝寰楀埌澶╂暟;3銆佽缃瓹鍒楀崟鍏冩牸鏍煎紡涓衡滃父瑙勨濓紝...
绛旓細Excel涓殑鏃ユ湡鏈川涓婃槸鏁板瓧锛屾瘡涓涓棩鏈熼兘瀵瑰簲涓涓暟瀛楀硷紝杩欎娇寰楁棩鏈熺殑鍔犲噺璁＄畻鍙樺緱鐩稿绠鍗曘傚叿浣撴潵璇达紝濡傛灉浣犳兂瑕佸皢鏃ユ湡鍚戝墠鎴栧悜鍚庣Щ鍔紝浣犲彧闇瑕佽繘琛岀浉搴旂殑鍔犲噺杩愮畻鍗冲彲銆備緥濡傦紝濡傛灉浣犳湁涓涓棩鏈熷湪A1鍗曞厓鏍硷紝浣犳兂鎶婅繖涓棩鏈熷悜鍓嶇Щ鍔ㄤ笁澶╋紝浣犲彲浠ヤ娇鐢ㄥ叕寮“=A1-3”銆傚悓鏍凤紝濡傛灉浣犳兂鎶婅繖涓...
