[Excel] 外商必備換算工具-日期週數轉換



之前寫了一篇文章介紹如何把週數轉換成對應的月份，這次要來更完整的換算了！
換算的工具還是使用Excel，畢竟他還是目前企業普遍使用的文書處理工具。

假設今天日期為2020/12/25要怎麼把它換成週數呢？去查日曆的話，就會知道這天是在2020年的第52週的星期一，外商大多會簡潔表示就會變成"2052.5"，那要怎麼把日期換算成週數呢？



如果A2格填的是日期，那在A3格輸入以下算式，就可以作轉換了！

=RIGHT(YEAR(A2),2)&IF(WEEKNUM(A2,2)<10, TEXT(WEEKNUM(A2,2),"00"),WEEKNUM(A2,2))&"."&WEEKDAY(A2,2)

 那這這個算式要怎麼解讀呢？

  1. 2052.1：主要分成四個部分，"20", "52", ".", "5"，他們之間用"&"符號來連接，"&"符號在Excel是用來連結字串。
  2. RIGHT(YEAR(A2),2): 這個式子會產出"20"這個數字，函式意思是把A2的日期用YEAR函式把年份取出，也就是2020年，但是我們只要取2020年最後面的20來表示就好，所以就用RIGHT函式來擷取2020這個數字的最右邊兩個數字，RIGHT函式裡面的2，就是表示取兩個數字！
  3. IF(WEEKNUM(A2,2)<10, TEXT(WEEKNUM(A2,2),"00"),WEEKNUM(A2,2)): 這個式子會產出“52”這個數字，也就是日期的週數，只不過為了維持週數的表示都有兩個位數，多用了IF這個函式來判斷週數為個位數還是雙位數。比方說第一週，我們希望它顯示01而非1。
  4. WEEKDAY(A2,2)：這個式子就單純許多了，他就是在算日期會是在星期幾。

但是，有時候還是會需要由週數知道對應的日期為多少，假設我們要把A4格裡的2052.5轉乘日期的話，這時候相反的換算就可以用下面這個式子：

=DATE(2000+LEFT(A3,2),1,1)+7*(MID(A3,3,2)-1)+(RIGHT(A3,1)-WEEKDAY(DATE(2000+LEFT(A3,2),1,1),2))

記得要把儲存格的格式轉成"日期"來表示！


  1. DATE(2000+LEFT(A3,2),1,1): 這個式子會產生出2020/1/1，年的算法就是2000＋20，20就是用LEFT這個函式來取出，2052.5這個數字的最左邊的兩個位數。
  2. 7*(MID(A3,3,2)-1): 這個式子會產生出(52-1)*7這個數字，基本上就是在算2052.5距離2020/1/1有多少天。用MID這個函式來取出2052.5中的52這個週數出來。
  3. (RIGHT(A3,1)-WEEKDAY(DATE(2000+LEFT(A3,2),1,1),2)): 這個式子是在算2052.5的5(星期五)跟2020/1/1(星期三)的星期差距幾天。
這個式子有個要注意的點，如果你是要看2000年前的轉換，要記得把式子裡的2000改得更小一點！不然會有問題！

式子的解讀看不懂沒關係，下面就有範例分享直接拿去用就好了！


留言

張貼留言

這個網誌中的熱門文章

[理財/記帳]google表單結合iphone捷徑 自製記帳app

圖片
使用 google表單記帳 ，不代表一定要使用筆電開網頁，或者一定要儲存表單連結在手機裡某記事本才能開啟。利用手機建立捷徑的方式，可以做出像app一樣，點開就可以記帳了！整個過程完全免費，而且可以完全依自己的需求來做調整。 下方為如何使用IOS系統，來一步一步建立google表單記帳捷徑 製作捷徑 1. 手機裡搜尋“捷徑” 2. 點選製作捷徑 3. 點選“加入動作” 4. 點選“網頁” 5.  在 Safari 裡點選“打開URL” 6. 在黃色箭頭處，輸入你的表單網頁網址 7. 接著，可以幫你的捷徑命名，命名完按“完成”，就好了！ 這樣就可以在apple的widget裡，放入捷徑，點選記帳捷徑，就會自動開啟google表單網頁了！ 製作捷徑app 1. 點選剛剛建立的記帳捷徑，右上方的三個點 2. 進入後，再點選黃色箭頭處的三個點 3. 進入下方圖片的畫面後，點選“加入主畫面” 4. 點選黃色箭頭處的圖標，可以選擇自己的圖片當作app的圖案。 最後再按“加入”以後，就會有個人化的app捷徑囉！
閱讀完整內容

[理財/記帳]利用google表單記帳雲端化 - 免費模板下載@ Mimi's learning notes

圖片
在 [理財/記帳]懶人記帳-現金流+信用卡管理記帳法-不用天天記帳 的文章裡裡面，我介紹了如何利用現金流的概念加上妥善的信用卡管理，來進行記帳分類，以減少記帳次數，省時省事，同時追蹤自己的財務狀況。 在這一篇我想分享我如何使用google表單來製作我的記帳問卷，最後面會有免費的google表單記帳模板，覺得有需要的人可以去試試看。 使用google表單來記帳，有非常多好處。它是免費的，你只要有google帳號就可以免費使用，它也可以依自己的需求做很彈性的調整設定外，最強大的就是它會自動把問卷內容整理進google sheet，而google sheet跟Excel的功能非常接近，當然google sheet的功能還是比Excel陽春一些，但它已經可以完美滿足我們整理記帳資料的需求。 這份表單內，我們共會有5個區段： 可以先於下方圖片內箭頭處，新增其他四段區段。 區段1 - 記帳日期＋消費類型分類 第一個問題當然是要紀錄記帳當天的日期！ 第二個問題，就是根據 [理財/記帳]懶人記帳-現金流+信用卡管理記帳法-不用天天記帳 文章裡的內容進行分類，主要就是分成四類： 信用卡 領錢 (銀行或ATM領取的現金） 轉帳 收入 在每個選項後，選擇“前往不同的區段”，並請將問題設定為“必填”。這樣選擇不同的選項，就會前往下一個特定區段，去填寫不同的問題。 區段2 - 信用卡分類 信用卡區的第一個問題是，你要填哪張信用卡的消費。可以新增“其他”選項，這樣之後有新增的信用卡時，也可以在表單上直接填寫。 第二個問題則是要問信用卡消費的金額，以簡答的方式填寫，並將簡答限定以數字的方式呈現，可以避免一些錯誤。 第三個問題則是問繳交信用卡帳單所產生的手續費，因為繳交信用卡費，可能是用轉帳的方式繳交，有些轉帳的過程會產生手續費。選項可以新增“其他”選項，這樣手續費如果不是15或是0元，就可以自己在表單填寫。 最後請記得在區段2的尾端，設定“提交表單”，這樣才不會跑到其他的區段問題。 區段3 - 實領現金 區段3的實領現金，是指從銀行或是ＡＴＭ提領出來的現金金額。 第一個問題是問領出來的金額，問題答案一樣限制為數字形式。 第二個問
閱讀完整內容

[Excel]年份週數換算成月份

圖片
在外商公司工作，公司都用週數來管理時程，初來乍到之時，實在好難轉換什麼週數對應什麼月份.... 其實只要用簡單的公式就可以算出來囉！ 下圖，從B1一直到N1都是月份的數值，是根據他們的下一排去做換算的。 19: 2019年。 後兩數為週數。 換算請在B1輸入算式：=MONTH(DATE(ROUNDDOWN(B2/100,0),1,1)+MOD(B2,100)*7-1) 這算式分成兩部分： DATE(ROUNDDOWN(B2/100,0),1,1): 這個算式是將B2的“1901”換算回日期，ROUNDDOWN是無條件捨去，所以就是取1901/100的除數。 MOD(B2,100)*7: MOD傳回1901和100相除後的餘數再乘7。 -1: 可以定義一個月的起點是星期幾，-1表示從星期一開始。 最後再把上面三個數值用函式MONTH換算成月份。 附上範例供大家學習和使用： https://drive.google.com/file/d/1W_41Zg0t0jBQSLIRHm7gHq8-8DHaoyYB/view?usp=sharing 
閱讀完整內容