被問到:在 Excel 裡如何將日期時間字串依時區轉換?

如下圖,其中有一個日期和時間的清單,要依其內容轉換為「澳洲」的時區所對照的日期和時間。注意:原始日期和時間之間有被串接文字「T」。

image

【公式設計與解析】

假設時區轉換規則:

(1) 時間:如果時間加11小時已是隔天,則時間不變,如果不是,則時間加11小時。

(2) 日期:如果時間加11小時已是隔天,則日期加1,如果不是,則日期不變。

1. 處理轉換後時間

儲存格B3:=IF(MID(A3,12,2)+11>24,MID(A3,12,8)*1,MID(A3,12,8)*1+11/24)

複製儲存格B3,貼至儲存格B3:B23。

利用 MID(A3,12,2) 函數取出「小時」,利用 MID(A3,12,8) 函數取出「時間」,利用「*1」將文字格式的時間轉換為數值,利用「+11/24」執行時間加11小時。

2. 處理轉換後日期

儲存格C3:=IF(MID(A3,12,2)+11>24,MID(A3,1,10)*1+1,MID(A3,1,10)*1)

利用 MID(A3,12,2) 函數取出「小時」,利用 MID(A3,1,10) 函數取出「日期」,*1」將文字格式的日期轉換為數值。

複製儲存格C3,貼至儲存格C3:C23。

3. 處理星期

儲存格D3:=TEXT(C3,"[$-404]aaaa;@")

複製儲存格D3,貼至儲存格D3:D23。

利用 TEXT 函數與參數「[$-404]aaaa;@」,可以將數值的日期顯示為「星期N」的格式。

學不完.教不停.用不盡文章列表

arrow
arrow
    文章標籤
    Excel 日期 時間 時區
    全站熱搜

    vincent 發表在 痞客邦 留言(0) 人氣()