EXCEL怎样设置每月15日前（含15日当天）入职自动判定为新入职员工于当月参保，15日后入职的在次月参保？ 请问，如何在EXCEL自动体现月数和年数呢 比如入职日期在1...

\u6bcf\u670815\u65e5\u4e4b\u524d\uff08\u542b15\u65e5\uff09\u5165\u804c\u7684\u5458\u5de5\uff0c\u5165\u804c\u6708\u4efd\u8bb0\u4e3a\u5f53\u6708\uff1a15\u65e5\u4ee5\u540e\u5165\u804c\u7684\u5458\u5de5\u5165\u804c\u6708\u4efd\u8bb0\u4e3a\u6b21\u6708\uff0c\u8fd9\u4e2aEXCEL\u7684

=if(day(a1)<=15,month(a1),if(month(a1)=12,1,month(a1)+1)) ,a1\u6307\u7684\u662f\u5165\u804c\u65e5\u671f\u90a3\u4e00\u5217

\u5982\u56fe\u3002
B4\u7684\u516c\u5f0f\uff1a =IF(YEAR($B$1)-YEAR(A4)>1,12,MONTH($B$1)-MONTH(A4)+IF(DAY(A4)<15,1,0))
C4\u7684\u516c\u5f0f\uff1a =YEAR($B$1)-YEAR(A4)

在C2中输入或复制粘贴下列公式

=DATE(YEAR(B2),MONTH(B2)+(DAY(B2)>15),1)

或

=IF(DAY(B2)>15,DATE(YEAR(B2),MONTH(B2)+1,1),EDATE(B2,0))

下拉填充

=IF(AND(DAY(A1)>15,YEAR(A1)=YEAR(TODAY())),IF(MONTH(TODAY())-1=MONTH(A1),"当月参保",""),IF(AND(MONTH(TODAY())=MONTH(A1),YEAR(A1)=YEAR(TODAY())),"当月参保",""))自动根据时间判断是否本月参保

入职日期在A列，参保情况在B列，公式写在B1单元格，可以直接复制粘贴使用，
公式这么写:
=if(day(A1)>=15,"次月参保","本月参保")
如有疑问可以继续交流！

您好！我们首先使用day函数提取日期，再使用if函数判断大于14天的为“次月参保”，否则“当月参保”。请看视频：

EXCEL鎬庢牱璁剧疆姣忔湀15鏃ュ墠(鍚15鏃ュ綋澶)鍏ヨ亴鑷姩鍒ゅ畾涓烘柊鍏ヨ亴鍛樺伐浜庡綋鏈堝弬...
绛旓細=IF(DAY(B2)>15,DATE(YEAR(B2),MONTH(B2)+1,1),EDATE(B2,0))涓嬫媺濉厖

姹傚姪:Excel鎬庢牱鐢ㄥ叕寮忓疄鐜颁粠骞存湀鏃ヤ腑鎻愬彇淇℃伅,姣忔湀15鏃ュ墠鏄剧ず鏈勾鏈,姣 ...
绛旓細=DATE(YEAR(H),IF(DAY(H1)<=15,MONTH(H1),MONTH(H1)+1),1)鐒跺悗鎶奊鍒楁棩鏈熸牸寮忚缃垚YYYY-MM灏卞ソ浜嗘棩鏈熼殢浠栧幓鍚

濡備綍鍦excel涓璁＄畻宸ラ緞鏈堜唤,鍙鏈堜唤(15鍙蜂箣鍓绠楀綋鏈,15鍙蜂互鍚庣畻涓嬫湀...
绛旓細=DATEDIF(A1,DATE(YEAR(TODAY()),MONTH(TODAY()),14),"m")鍏紡鍙互涓嬫嫋澶嶅埗锛侊紒锛

excel鎬庝箞璁＄畻涓骞村唴涓婄彮鐨勬湀鏁,鎸夌収15鏃ュ墠璁1涓鏈,15鏃ュ悗璁0.5涓湀
绛旓細=(YEAR(TODAY())-YEAR(A1))*12+IF((MONTH(TODAY())-MONTH(A1))>=0,MONTH(TODAY())-MONTH(A1),MONTH(TODAY())-MONTH(A1)-12)+IF((DAY(TODAY())-DAY(A1))>=15,1,IF((DAY(A1)-DAY(TODAY()))>=15,-1,0))鍙傝冨叕寮 鍙傝冪綉鍧:http://wenwen.sogou.com/z/q539509978.htm ...

濡備綍鐢excel姹備笉鍚岄」鐩15鍓嶅叆鑱屼笌15鏃ュ悗鍏ヨ亴鐨勪汉鏁板拰,鏈夊浘鐗
绛旓細1銆佸彲浠ュ缓绔嬩竴涓緟鍔╁垪鈥 鍏ヨ亴鏃堕棿 " 杈撳叆鍏紡锛=DATEDIF(C6,TODAY(),"d")鍏跺惈涔夋槸C6鍗曞厓鏍兼棩鏈燂紝涓庝粖澶﹖oday锛堬級鏃ユ湡鐩稿噺鎵寰楃殑澶╂暟锛屸漝鈥滆〃鏄ぉ鏁帮紝鈥滿鈥滆〃绀烘湀鏁帮紝Y琛ㄧず骞 2銆佸啀鍒╃敤countif锛堬級鍑芥暟杩涜缁熻 2.1缁熻15鏃ュ墠鐨 2.2缁熻15鏃ュ悗鐨 鏈汉灏卞緱锛岀◢鏈夌偣楹荤儲锛屼絾鏄柟渚跨悊瑙ｏ紝鐢...

EXCEL濡備綍鍒ゆ柇鍏ヨ亴鏃ユ湡鏄惁灏戜簬鏈鏈15鏃
绛旓細濡傛灉浣犱粎鍒ゆ柇鍏ヨ亴鏃ユ湡鏄惁灏忎簬鏈鏈15鏃ワ紝鍏ヨ亴鏃ユ湡涓篈1锛岀敤鍏紡 =IF(DAY(A1)<15,"15鏃ュ墠","15鏃ュ悗")浣嗙湅浣犵殑闂搴旇鏄互鍏ヨ亴鏃ユ湡鍒ゆ柇鏈湀涓婄彮鎴栧嚭鍕ゅぉ鏁帮紝鍗宠绠楀叆鑱屾棩鏈熷埌鏈湀搴曠殑澶╂暟锛屽彲鐢ㄥ叕寮忥細=DATEDIF(A1,DATE(YEAR(A1),MONTH(A1)+1,0),"d")

璇烽棶鎴戣繖涓EXCEL琛ㄦ牸閲岃绠楁湀浠,濡傛灉鍦15(鍚15)鏃ュ墠绠1涓鏈,15鏃ヤ互鍚...
绛旓細浣犱妇鐨勪緥瀛愶紝鈥2018骞12鏈鈥濆簲璇ユ槸鈥2018骞12鏈31鏃モ濆惂锛=DATEDIF(A1,B1,"m")+IF(DAY(A1)<15,1,0.5)

EXCEL濡備綍鍒ゆ柇鍏ヨ亴鏃ユ湡鏄惁灏戜簬鏈鏈15鏃
绛旓細濡傛灉浣犱粎鍒ゆ柇鍏ヨ亴鏃ユ湡鏄惁灏忎簬鏈鏈15鏃ワ紝鍏ヨ亴鏃ユ湡涓篈1锛岀敤鍏紡 =IF(DAY(A1)<15,"15鏃ュ墠","15鏃ュ悗")浣嗙湅浣犵殑闂搴旇鏄互鍏ヨ亴鏃ユ湡鍒ゆ柇鏈湀涓婄彮鎴栧嚭鍕ゅぉ鏁帮紝鍗宠绠楀叆鑱屾棩鏈熷埌鏈湀搴曠殑澶╂暟锛屽彲鐢ㄥ叕寮忥細=DATEDIF(A1,DATE(YEAR(A1),MONTH(A1)+1,0),"d")...

EXCEL閲岄潰鍦鏈銆鏃ュ墠缁熶竴鍔犲勾浠藉悧?
绛旓細EXCEL閲岄潰鍦鏈銆鏃ュ墠缁熶竴鍔犲勾浠藉晩鐨勫叿浣撴楠ゅ涓嬶細鎴戜滑闇瑕佸噯澶囩殑鏉愭枡鍒嗗埆鏄細鐢佃剳銆Excel琛ㄦ牸銆1銆侀鍏堟垜浠墦寮闇瑕佺紪杈戠殑Excel琛ㄦ牸銆2銆佺劧鍚庢垜浠偣鍑籇1鍗曞厓鏍艰緭鍏ワ細=DATE(2009,MONTH(C1),DAY(C1))锛屼箣鍚庡洖杞︾‘瀹氥3銆佺劧鍚庢垜浠偣鍑籇1鍗曞厓鏍硷紝灏嗛紶鏍囧厜鏍囨斁鍒板崟鍏冩牸鐨勫彸涓嬭锛屽嚭鐜伴粦鑹插崄瀛楀浘鏍囦箣鍚庯紝鎸変綇...

excel濡備綍绛涢10鍙蜂箣鍓鐨勬椂闂存
绛旓細1銆佹墦寮鐢佃剳妗岄潰锛屽弻鍑昏绛涢夌殑excel琛ㄦ牸锛屽涓嬪浘鎵绀猴細2銆佹墦寮涔嬪悗锛岄変腑鏃ユ湡閭ｄ竴鍒楋紝鐒跺悗鐐瑰嚮涓婃柟鐨勭瓫閫夊浘鏍囷紝濡備笅鍥炬墍绀猴細3銆佺偣鍑绘棩鏈熷彸涓嬭鐨勪笁瑙掑舰鍥炬爣锛屽湪寮瑰嚭鐨勮彍鍗曚腑锛岄夋嫨鏃ユ湡绛涢夐夐」锛屽涓嬪浘鎵绀猴細4銆侀夋嫨涔嬪悗锛屽湪寮瑰嚭鐨勫璇濇涓紝鏍规嵁鑷繁闇姹傚湪鏃ユ湡涓嬫媺妗嗕腑閫夋嫨绛涢夋棩鏈熺殑鏉′欢锛屽湪杈撳叆妗嗕腑...