excel农历怎么转换为公历？
Excel农历转公历公式，网上几乎搜不到，我自己花功夫还是慢慢摸索出来了，如图：
Excel 农历转公历
比如要查找的农历日期为今年的“三月十五”，那就先将A列的格式从“常规”改为“文本”，将B列的格式从“常规”改为“日期”，然后在A2单元格内输入【2022-3-15】，再在B2单元格内输入如下等式：
=IFERROR(SMALL(IF(TEXT(ROW(INDIRECT(DATE(LEFT(A2,4),1,1)&":"&DATE(LEFT(A2,4),12,31))),"[$-130000]YYYY-M-D")=A2,ROW(INDIRECT(DATE(LEFT(A2,4),1,1)&":"&DATE(LEFT(A2,4),12,31))),""),1),SMALL(IF(TEXT(ROW(INDIRECT(DATE(LEFT(A2,4)+1,1,1)&":"&DATE(LEFT(A2,4)+1,12,31))),"[$-130000]YYYY-M-D")=A2,ROW(INDIRECT(DATE(LEFT(A2,4)+1,1,1)&":"&DATE(LEFT(A2,4)+1,12,31))),""),1))
最后三键回车，Excel会自动计算后显示结果为【2022-4-15】，这个就是对应【2022-3-15】的公历日期。
望采纳。
绛旓細=LOOKUP(--P7,--TEXT(P7+ROW(10:60),"[$-130000]yyyy-m-d"),P7+ROW(10:60))鍏紡鐨勬濊矾灏辨槸浠嶱7涓殑鏃ユ湡寮濮嬶紝寰鍚庢帹10鍒60澶╋紝灏嗘鑼冨洿鍐呯殑鏃ユ湡杞崲涓哄啘鍘鏃ユ湡锛屽啀鐢↙OOKUP鏌ユ壘P7鍦ㄨ繖浜涜浆鎹㈠悗鏃ユ湡涓殑浣嶇疆锛岃繑鍥炲搴旂殑鍏巻鏃ユ湡銆傛棤璁濡備綍锛屽叕鍘嗘棩鏈熶笉鍙兘姣斿啘鍘嗗彧鍚10澶╀互鍐呮垨鍦60澶╀互澶...
绛旓細2007鐗堢殑excel鍙敤涓嬮潰鍏紡杞崲锛=TEXT(A1,"[$-130000]yyyy骞磎鏈"&IF(LEN(--TEXT(A1,"[$-130000]dd"))=1,"鍒","")&"d")
绛旓細鍦Excel涓澶勭悊鏃ユ湡鏃讹紝鍏巻杞啘鍘鐨勬搷浣滃苟涓嶉渶瑕佸熷姪澶栭儴宸ュ叿锛孍xcel鑷韩灏辨彁渚涗簡杞崲鍔熻兘銆備笅闈㈠皢鏁欎綘濡備綍鍒╃敤鍐呯疆鍑芥暟瀹炵幇杩欎竴杞崲銆備互涓涓畝鍗曠殑渚嬪瓙璇存槑锛屽亣璁句綘鐨勫叕鍘嗘棩鏈熷湪A鍒楋紝鑰屽啘鍘嗘棩鏈熷湪鐩搁偦鐨勫垪銆傞鍏堬紝鍦ㄥ啘鍘嗘棩鏈熷崟鍏冩牸涓緭鍏ュ叕寮忥細=TEXT(A2, "[$-130000]yyyy-m-d")杩欓噷鐨"A2"鏄綘瑕...
绛旓細鍙互杞笉杩囧叧绯诲お澶嶆潅涓鑸殑闇瑕佷娇鐢ㄤ笓闂ㄧ殑鏁版嵁琛紝寤鸿鐢╲ba鏁版嵁琛ㄦ潵澶勭悊锛岃嚜宸辩櫨搴鍐滃巻杞鍏噷鍘嗗氨鍙互浜
绛旓細鍦ㄦ棩甯稿伐浣滀腑锛屾垜浠粡甯镐細浣跨敤鍒板悇绉嶅姙鍏蒋浠讹紝灏ゅ叾鏄疎xcel杞欢銆傛垜浠粡甯搁渶瑕佸湪Excel涓叕鍘嗚浆鍐滃巻锛岄偅涔堬紝鎴戜滑濡備綍鍦‥xcel涓叕鍘嗚浆鍐滃巻鍛紵浠婂ぉ灏忕紪灏辨暀鍚勪綅璁剧疆Excel涓叕鍘嗚浆鍐滃巻鐨勫皬鎶宸э紝澶у涓璧锋潵瀛︿範鍚э紒绗竴姝ワ細鍙屽嚮鎴戜滑闇瑕佹搷浣滅殑Excel琛ㄦ牸锛岀偣鍑婚渶瑕佺紪杈戠殑鍗曞厓鏍硷細 绗簩姝ワ細鍦ㄥ啘鍘嗕竴鏍忎腑锛...
绛旓細D3杈撳叆=TEXT(C3,"[$-130000]yyyy-mm-dd")璇曡瘯鐪 濡傛灉涓嶆槸闈炵敤Excel 閭ｅ氨鎵嬫満搴旂敤閲屾湁鐢熸棩绠＄悊杞欢瑙ｅ喅鍚
绛旓細6(R+1)+骞村唴鏃ユ湡搴忔暟-29.5n 锛堟敞:寮忎腑Q銆丷銆乶鍧囦负鑷劧鏁帮紝R 闃冲巻闃村巻鍗佷簩鏃跺鐓ц〃 闃村巻闃冲巻浜屽崄鍥涙柊鍘嗗啘鍘嗗鐓ц〃 涓囧勾鍘嗘崲绠椻曗曢槾鍘嗛槼鍘嗚浆鎹佸叕鍘嗗啘鍘嗗鐓ц〃 鎻愪緵涓囧勾鍘嗘崲绠楁煡璇⑩曗曟櫤鑳杞崲闃村巻闃冲巻銆佸叕鍘嗗啘鍘嗗鐓ц〃锛佸彲浠ュ悓鏃舵樉绀哄叕鍘嗐佸啘鍘嗐佽妭姘斻佸浗闄呪︹︿竾骞村巻- 鍏巻鍐滃巻闃村巻杞崲-鐨囧巻...
绛旓細鍦EXCEL涓鎶鍏巻杞崲鎴愬啘鍘鐨勬柟娉曞強鍏蜂綋鎿嶄綔姝ラ濡備笅锛屼互WPS涓璄XCEL涓轰緥鍋氭紨绀恒1銆佸湪鐢佃剳涓墦寮闇瑕佹搷浣滅殑EXCEL琛ㄦ牸锛屽闇瑕佸皢A1鐨勬棩鏈杞垚鍐滃巻锛屽涓嬪浘鎵绀恒2銆佺偣鍑讳笂鏂硅彍鍗曟爮涓殑寮濮嬮夐」锛岃繘鍏ヤ笅涓椤甸潰銆3銆佺偣鍑诲彸渚х殑姹傚拰閫夐」涓嬫媺鑿滃崟锛岄夋嫨鈥滃叾浠栧嚱鏁扳濋夐」锛岀偣鍑昏繘鍏ヤ笅涓椤甸潰銆4銆佸湪寮瑰嚭鐨勫璇...
绛旓細[DBNum1]m鏈坉鏃"鍗冲彲銆excel鍏巻杞啘鍘鏂规硶涓夛細浣跨敤text+MID+CHOOSE+YEAR鍑芥暟 A1涓哄叕鍘锛孊1杈撳叆锛=CHOOSE(MOD(YEAR(A1)-1900,10)+1,"搴","杈","澹","鐧","鐢","涔","涓","涓","鎴","宸")&CHOOSE(MOD(YEAR(A1)-1900,12)+1,"瀛","涓","瀵","鍗","杈","宸"...
