鑫大叔

一個常年在工廠打工的產品工程師。希望除了在這裡和大家分享書籍讀後感,還能找回點當年人人網寫日記的感覺,偶爾還會發些Excel小技巧~

在Excel把時間轉換成值班日

發布於
在探路客發佈(2021年4月30日)的第一篇,也是最後一篇文章。

製造業數據都是用時間記錄(以秒爲單位),但是做圖表或者分析的時候常常需要以值班日期顯示。所以今天我想和大家分享的就是如何把實際的日期和時間換成值班日期。

沒在工廠做過的朋友可能不清楚,對於7天24小時都在跑的工廠,需要作業員12小時輪班。值夜班的人是從晚上7:30pm做到隔天早上7:30am,在早上7:30am之前,值班日算是前一天。例如現在是4月29號晚上11點17分,等下過了12點就是4月30號,但是值班日期還是4月29號,直到早上7點半。

講到這裏可能你已經知道這招的難點在哪裏了,就是要讓午夜12點到早上七點半之間的日期顯示爲前一天。通常資料類型會有兩種:

🔷 第一種:日期和時間在不同列(column)

資料類型1:日期和時間在不同列(column)
值班日期:在C2格輸入“=IF(B2<0.3125,A2-1,A2)”,即可獲得值班日期

❇️ 使用函數説明

  • IF是用來判斷時間是否小於7點半,如果是的話就取前一天的日期,反之就直接使用
  • 0.3125代表的是7.5小時等於多少天,計算方式為7.5小時除以24小時(7.5/24)


🔷 第二種:日期和時間在同一列

資料類型2:日期和時間在同一列
半成品:在B2格輸入=(IF(HOUR(A2)*100+MINUTE(A2)<730,A2-1,A2))
值班日期:在C2格輸入=TEXT(B2,"yyyy-mm-dd")-0

❇️ 半成品使用函數説明

  • 這個比較複雜,爲了簡化過程,我們多用一列做半成品。這個半成品除了使用IF函數,還用了HOUR和MINUTE函數。
  • HOUR和MINUTE分別可以幫助我們獲得幾點幾分,範例是凌晨1點41分,我們用HOUR取得1,MINUTE取得41,所以我們把1乘以100再加上分鐘,就等於141,小於730(換班時間),所以日期需要減一,反之就不用.

❇️ 值班日期使用函數説明

  • 這個半成品日期雖然正確了,但是附帶了時間,所以我們需要用TEXT函數去掉時間。範例裏面的yyyy-mm-dd代表了要顯示格式為年月日,最後減去零是爲了把文字格式轉換回數字格式。如果你不需要數字格式就可以不用減零。


爲了方便理解,附上Excel檔案以供參考,點我下載。今天的内容就到此爲止,如果有什麽不清楚的地方,歡迎留言,我會儘快回復,感謝你讀到這裏。

喜歡我的文章嗎?
別忘了給點支持與讚賞,讓我知道創作的路上有你陪伴。

CC BY-NC-ND 2.0 版權聲明

晚上十一點收到關於Excel的求救電話

鑫大叔Excel小技巧 | Vlookup的條件多於一個怎麽辦?

13

看不過癮?

一鍵登入,即可加入全球最優質中文創作社區