excel日期轉換第幾周
本文已影響2.05W人
本文已影響2.05W人
excel日期轉換第幾周,excel在辦公的時候都是會使用到的,而且在日常工作中經常會使用excel,有時需要將日期轉換為星期,不需要一個一個手動輸入,下面瞭解excel日期轉換第幾周及相關資料。
首先我們在表格中添加本月1號的單元格,並計算它是對應星期幾。
求星期幾可以直接使用weekday函數,但需要選擇參數,通常我們習慣一週從週一開始,所以選擇參數2——“從星期一到星期天”,它的結果會顯示為1-7的數字。
從下圖中得知,12月1日是星期3,並不是星期1,但日曆中當月第一週實際還是從週一開始的,所以我們要往回追溯到第一週週一的時間。
通過1號的時間減去星期3的數字3,再加上1才等於星期1,而星期1對應的日期可以在下圖中看到,是11月29日。
得到了本月第一週週一的日期,便可以用今天的日期減去該日期。因為不管第一週週一從哪天開始,它都是七天。
於是得到兩者相減的結果,等於20,但實際天數是包含了兩個日期當天的,因此還需要加上1,結果為21。
這個21就是從第一週的週一開始到當前日期的天數,我們除以一週7天,等於3。
而當它們相除的結果出現餘數時,則表示越過了這一週,到下一週了。
那麼我們可以通過一個函數,來向上取整,如下圖所示:=ROUNDUP(D10/7,0.01)
roundup函數即向上取整函數,第二個參數作者設置為0.01,即只要小數點後面大於0.01,就自動向上取整。
如果不添加下面的輔助説明,那麼使用一個完整的公式來計算,即為:=ROUNDUP((B4-(B6-WEEKDAY(B6,2)+1)+1)/7,0.01)。
但通過上面的步步分解,能夠便於童鞋們理解。
綜上所述,其實作者君的思路就是不管1號是周幾,今天是周幾,而從第一週的週一開始計算天數,有了第一週週一到當前日期的天數,後面就很好計算了。
然後今天使用到了兩個函數,ROUNDUP和WEEKDAY函數,一個是數學函數,一個是日期函數,都屬於excel中較為常用的'函數,童鞋們可以作一些瞭解。
方法一 :
選中A1,鼠標右鍵,選擇【 設置單元格格式 】,在【 自定義 】中輸入 aaaa ,這樣A1單元格將返回如“星期三”這種表示方式。
方法二:
選中A1,鼠標右鍵,選擇【 設置單元格格式 】,在【 自定義 】中輸入 dddd, 這樣A1單元格將返回如“Wednesday”這種英文表示方式。
方法三:
選中A1,鼠標右鍵,選擇【 設置單元格格 式】,在【 自定義 】中輸入“ 周aaa ”,這樣A1單元格將返回如“週三”這種表示方式。
如果用公式 =TEXT(A1,"aaaa") ,一樣可以達到更改格式顯示的目的。
方法四:
雙擊B1,輸入函數 :=WEEKDAY(A1,2), 將返回2014年5月7日在一個星期中的序號為3。第二參數為2表示一週以週一為第一天。
方法五:
雙擊B1,輸入函數 :=WEEKDAY(A1,1), 將返回2014年5月7日在一個星期中的序號為4。第二參數為1表示一週以週日為第一天。
一、獲取“年月日”信息
獲取“年月日”的信息比較簡單,方法也比較多,用單一的函數(如TEXT函數)就可以實現,現舉例如下:
=TEXT(A2,"YYYY年")
=TEXT(A2,"M月")
=TEXT(A2,"d日")
需要注意的是用TEXT函數提取到的“年月日”信息是文本型的,無法直接進行計算,如果要進行計算,一般情況下要分別用YEAR、MONTH、DAY三個函數。舉例如下:
返回數值型的年份:=YEAR(A2)
返回數值型的月份:=MONTH(A2)
返回數值型的日:=DAY(A2)
二、獲取“週數”信息
獲取“週數”的信息就要複雜一些,雖然WEEKNUM函數可以直接返回一個日期對應的週數,但WEEKNUM函數是將1月1日所在的周視為一年中的第一週。然而,在實際應用中則是將包含新的一年中天數較多(四天或更多)的一週定義為第一週。這表明對於三天或少於三天的一月份的第一週,按照實際應用,WEEKNUM函數返回的週數是錯誤的。不過這可以用函數和公式來調整和避免的。
1、將1月1日所在的周視為第一週
這樣的“週數”很簡單,直接用WEEKNUM函數即可:
=WEEKNUM(A2,2)
2、新的一年中天數較多的一週為第一週
這樣的“週數”就不能直接用WEEKNUM函數,而是需要進行計算,公式如下:
=IF(WEEKNUM(A2,2)-1=0,WEEKNUM(DATE(YEAR(A2)-1,12,31),2),WEEKNUM(A2,2)-1)
三、獲取“工作日”信息
這裏的“工作日”是指每週的周幾信息,也相對比較簡單,直接用現在的函數WEEKDAY就可以了。不過這個函數返回的數值,需要通過其他函數和公式轉換成中文的顯示習慣,轉換的方法很多,大家可以仁者見仁,智者見智。我下面只是例舉其中一種方法。公式如下:
=LOOKUP(WEEKDAY(A2,2),{1,2,3,4,5,6,7;"星期一","星期二","星期三","星期四","星期五","星期六","星期日"})
四、獲取“季度”信息
Excel中沒有直接的函數可以返回一個日期對應的“季度”信息,因此這需要充分發揮各自的聰明才智來解決這個問題。我的方法是通過下面的公式來實現:
=LOOKUP(LEN(2^MONTH(A2)),{1,2,3,4;"一季度","二季度","三季度","四季度"})
上面的公式直接返回的是中文習慣的季度信息,如果只是需要數值型的季度信息,只需要以下公式就可以了:
=LEN(2^MONTH(A2))
Excel中日期的相互轉化還有很多方法,大家可以在日常工作中不斷總結、積累、分享。
跑男5開播時間幾月幾日 第五季所有嘉賓名單李易峯第幾期
excel提取指定文字
excel表格製作
excel表格製作斜線
Levercil口紅怎麼樣 Levercil是那個國家的牌子
adidasTERREX X RICO LEE 一“件”穿梭,敢行未來
excel日期格式轉換為文本 excel日期格式如何轉換為文本
code blue第三季什麼時候開播,code blue第三季收視率怎麼樣
code blue第三季什麼時候播出 code blue3好看嗎
怎麼破解excel權限
excel的實用技巧
excel寫保護怎麼破解
excel表格製作方法
excel函數公式大全
excel表格新手入門
h&m velvet lip cream 色號belle epoque試色
天王星自轉週期是多少天,15.6小時(公轉週期30685日)
excel表格快捷鍵常用
Excel常用公式大全
孕中期是幾周到幾周,孕中期是幾周
excel表格
excel快捷鍵大全常用
月工作計劃表excel
excel有什麼功能
母親節2023年幾月幾日 母親節是5月的第幾個星期日
recollect Forget----painrecollect為題短文
excel表格公式大全
excel按條件提取數據
excel公式詳解
Excel小技巧
excel快捷鍵大全
excel表格頂端不動
excel表格公式