狀況:如下網址
https://rogerhsu0622.pixnet.net/blog/post/120429358
之前有發問過的問題
一、https://ithelp.ithome.com.tw/questions/10212938
指令:
=TEXT(IF(WEEKDAY(DATE(2023,1,1))>1,DATE(2023,1,1)-WEEKDAY(DATE(2023,1,1))+1,DATE(2023,1,1))+(7*(ROW()-1)),"E年mm月dd日")&"~"&TEXT(IF(WEEKDAY(DATE(2023,1,1))>1,DATE(2023,1,1)+(7-WEEKDAY(DATE(2023,1,1))),DATE(2023,1,1)+6)+(7*(ROW()-1)),"E年mm月dd日")
問題
如上的指令,可以克服「無支援民國年顯示」?若無使用的話,我要如何下指令,我要顯示在同一個欄位顯示,如下網址,要借用其他欄位方式,那如下網址,是否可以解合如上指令?
https://vocus.cc/article/amp/6503eb93fd897800010225c4
A1:
=YEAR(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),1)+1+((ROW()-1)*7))-1911&"年"&TEXT(MONTH(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),1)+1+((ROW()-1)*7)),"00")&"月"&TEXT(DAY(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),1)+1+((ROW()-1)*7)),"00")&"日"&"-"&YEAR(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),1)+7+((ROW()-1)*7))-1911&"年"&TEXT(MONTH(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),1)+7+((ROW()-1)*7)),"00")&"月"&TEXT(DAY(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),1)+7+((ROW()-1)*7)),"00")&"日"
向下製到A2:A53儲存格。
C1:
=LEFT(TEXT(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),1)+1+((ROW()-1)*7),"YYYY/MM/DD"),4)-1911&MID(TEXT(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),1)+1+((ROW()-1)*7),"YYYY/MM/DD"),5,6)&"-"&LEFT(TEXT(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),1)+7+((ROW()-1)*7),"yyyy/mm/dd"),4)-1911&MID(TEXT(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),1)+7+((ROW()-1)*7),"yyyy/mm/dd"),5,6)
向下製到C2:C53儲存格。
113年8月19日~113年8月25日,我是用如下語法,還是不正確,然後我要修改月日的話,可以直接修改那部份?我最多四個星期而以,星期一至星期日。
=YEAR(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),1)+1+((ROW()-1)*7))-1911&"年"&TEXT(MONTH(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),1)+1+((ROW()-1)*7)),"00")&"月"&TEXT(DAY(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),1)+1+((ROW()-1)*7)),"00")&"日"&"-"&YEAR(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),1)+7+((ROW()-1)*7))-1911&"年"&TEXT(MONTH(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),1)+7+((ROW()-1)*7)),"00")&"月"&TEXT(DAY(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),1)+7+((ROW()-1)*7)),"00")&"日"
原公式是以一週開始為星期天,你是以星期一,所以會有誤差。
A1:
=YEAR(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+1+((ROW()-1)*7))-1911&"年"&TEXT(MONTH(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+1+((ROW()-1)*7)),"00")&"月"&TEXT(DAY(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+1+((ROW()-1)*7)),"00")&"日"&"-"&YEAR(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+1+7+((ROW()-1)*7))-1911&"年"&TEXT(MONTH(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+7+((ROW()-1)*7)),"00")&"月"&TEXT(DAY(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+7+((ROW()-1)*7)),"00")&"日"
民國顯示 2000 版本就有了
@窮嘶發發發
好像後面的版本有拿掉,至少我公司365版本現在藏在自訂裡。
後來又看了一下,還真的有...你是對的。
ccenjor iT邦高手 您好:
請問一下,需要改,例如2024年8月19日至2024年8月25日的話,如下指令需要改那一部份?
=YEAR(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+1+((ROW()-1)*7))-1911&"年"&TEXT(MONTH(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+1+((ROW()-1)*7)),"00")&"月"&TEXT(DAY(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+1+((ROW()-1)*7)),"00")&"日"&"-"&YEAR(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+1+7+((ROW()-1)*7))-1911&"年"&TEXT(MONTH(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+7+((ROW()-1)*7)),"00")&"月"&TEXT(DAY(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+7+((ROW()-1)*7)),"00")&"日"
你要做區間的話我建議還是學一下我提供的VBA方案。=ROCERA(LEFT(A1,FIND("-",A1)-1))&"至"&ROCERA(RIGHT(A1, LEN(A1)-FIND("-",A1)))
這樣一行就好。
((row()-1)改成33即可。
或((row()-1)改成weeknum(date(2024,8,19))-1
Froce
謝謝建議,您提供的方法,真讚。
Froce
謝謝建議,您提供的方法,真讚。
請問一下,全部((row()-1)全部都要改嗎?
對,才能抓對該週日期。
ccenjor iT邦高手 您好:
如下指令,我改的對不對?
=YEAR(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+1+weeknum(date(2024,8,19))-1)7))-1911&"年"&TEXT(MONTH(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+1+weeknum(date(2024,8,19))-17)),"00")&"月"&TEXT(DAY(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+1+weeknum(date(2024,8,19))-17)),"00")&"日"&"-"&YEAR(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+1+7+weeknum(date(2024,8,19))-17))-1911&"年"&TEXT(MONTH(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+7+weeknum(date(2024,8,19))-17)),"00")&"月"&TEXT(DAY(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+7+weeknum(date(2024,8,19))-17)),"00")&"日"
發現更簡單的方法,用TEXT就行了。
=TEXT(A1, "[$-zh-TW]e年m月d日")
結合你前一個問題,直接給你公式
DATE(2023,5,1)是開始日期
ROW()-1的1是你的第一筆資料開始的列數,第一列開始減1,第二列開始減2
=TEXT(DATE(2023,5,1)+(ROW()-1)*7,"[$-zh-TW]e年m月d日")&"-"&TEXT(DATE(2023,5,1)+(ROW()-1)*7+6,"[$-zh-TW]e年m月d日")
這樣直接往下拉就會從2023/5/1開始產生每個星期的區間,而且是民國紀年。
修正好的公式
=YEAR(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+1+((WEEKNUM(DATE(2024,8,19))-1)*7))-1911&"年"&TEXT(MONTH(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+1+((WEEKNUM(DATE(2024,8,19))-1)*7)),"00")&"月"&TEXT(DAY(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+1+((WEEKNUM(DATE(2024,8,19))-1)*7)),"00")&"日"&"-"&YEAR(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+1+7+((WEEKNUM(DATE(2024,8,19))-1)*7))-1911&"年"&TEXT(MONTH(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+7+((WEEKNUM(DATE(2024,8,19))-1)*7)),"00")&"月"&TEXT(DAY(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+7+((WEEKNUM(DATE(2024,8,19))-1)*7)),"00")&"日"
ccenjor iT邦高手您好
如上語法,下拉式的話,好像無法上畫面日期會變動?是我的office版本有問題嗎?
你試這個還不行,我也沒折。
B1:
=YEAR(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+1+(((WEEKNUM(DATE(2024,8,19))+ROW())-2)*7))-1911&"年"&TEXT(MONTH(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+1+(((WEEKNUM(DATE(2024,8,19))+ROW())-2)*7)),"00")&"月"&TEXT(DAY(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+1+(((WEEKNUM(DATE(2024,8,19))+ROW())-2)*7)),"00")&"日"&"-"&YEAR(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+1+7+(((WEEKNUM(DATE(2024,8,19))+ROW())-2)*7))-1911&"年"&TEXT(MONTH(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+7+(((WEEKNUM(DATE(2024,8,19))+ROW())-2)*7)),"00")&"月"&TEXT(DAY(DATE(2024,1,1)-WEEKDAY(DATE(2024,1,1),2)+7+(((WEEKNUM(DATE(2024,8,19))+ROW())-2)*7)),"00")&"日"
建議:
在某一儲存格填入預計開始日期(D1)公式會較省。
=MID(TEXT($D$1-WEEKDAY($D$1,2)+1+((ROW()-1)*7),"YYYY/MM/DD"),1,4)-1911&"年"&MID(TEXT($D$1-WEEKDAY($D$1,2)+1+((ROW()-1)*7),"YYYY/MM/DD"),6,2)&"月"&MID(TEXT($D$1-WEEKDAY($D$1,2)+1+((ROW()-1)*7),"YYYY/MM/DD"),9,2)&"日"&"-"&MID(TEXT($D$1-WEEKDAY($D$1,2)+(ROW()*7),"YYYY/MM/DD"),1,4)&"年"&MID(TEXT($D$1-WEEKDAY($D$1,2)+(ROW()*7),"YYYY/MM/DD"),6,2)&"月"&MID(TEXT($D$1-WEEKDAY($D$1,2)+(ROW()*7),"YYYY/MM/DD"),9,2)&"日"
新版excel有,藏在自訂格式裡。
VBA
alt + F11 (開啟編輯器) -> 在左邊點選你的工作簿 -> 右鍵插入模組 -> 貼上下面的程式碼
Function ROCERA(d)
Dim t() As String
t = Split(Format(d, "yyyy/m/d"), "/")
ROCERA = CStr(CInt(t(0)) - 1911) & "年" & t(1) & "月" & t(2) & "日"
End Function
以後這個活頁簿就能用函式 =ROCERA() 把西換成中華民國紀年。
要記得存成 xlsm
如果要以後每個活頁簿都能用,可以開個空白excel,一樣的操作但是存成excel增益集(xlam),去檔案->其他->選項->增益集->下方的管理 excel增益集 執行->把寫好的增益集載入。
這個VBA可以節省很多時間及空間。45523=2024/8/19
C1:
=ROCERA(DATEVALUE("2024/8/19")+((ROW()-1)*7))&"-"&ROCERA(DATEVALUE("2024/8/19")+((ROW())*7))
向下複製公式。
存檔時,沒有看見xlsm副檔名可以存檔
excel版本?
如果是舊版的excel,直接存成xls就行了。
另外用函式的話試試下面的
=TEXT(DATE(2023,5,1)+(ROW()-1)*7,"[$-404]e年m月d日")&"-"&TEXT(DATE(2023,5,1)+(ROW()-1)*7+6,"[$-404]e年m月d日")