メインコンテンツへスキップ

Excelで週番号を日付に、またはその逆に変換するにはどうすればよいですか?

Excelで特定の日付から週番号を取得したり、特定の週番号と年から日付範囲を抽出したりする方法はありますか? この課題を解決するために、次の公式があなたに有利になるかもしれません。

数式を使用して週番号を日付に変換する

数式を使用して日付を週番号に変換する


数式を使用して週番号を日付に変換する

次のスクリーンショットに示すように、ワークシートに2015年と15年のランダムな年と週の番号があり、月曜日から日曜日までの特定の日付をこの指定された週の番号で調べたいとします。

特定の週番号で日付範囲を計算するには、次の式を使用できます。

1。 開始日を返す空白のセルを選択し(この場合はセルB5を選択します)、数式を入力します。 =MAX(DATE(B1,1,1),DATE(B1,1,1)-WEEKDAY(DATE(B1,1,1),2)+(B2-1)*7+1)、を押して 入力します キー。 以下のスクリーンショットを参照してください。

2。 終了日を返す別の空白のセルを選択し(この場合はセルB6を選択します)、次のように入力します。 =MIN(DATE(B1+1,1,0),DATE(B1,1,1)-WEEKDAY(DATE(B1,1,1),2)+B2*7)、を押して 入力します キー。 以下のスクリーンショットを参照してください。

ノートリボン 数式は複雑すぎて覚えられませんか? 数式を定型句として保存して、後でワンクリックで再利用できるようにします。
続きを読む...     無料体験

注:上記の両方の式で、 B1 年が含まれ、 B2 は指定された週番号です。必要に応じて引数を変更できます)。

3。 ご覧のとおり、どちらの数式も日付ではなく数値を返します。 両方の計算結果を選択し続け、をクリックします ホーム > 数の書式 ボックス> 短い日付 数値を日付に変更します。 以下のスクリーンショットを参照してください。

ワンクリックで、Excelで複数の非標準フォーマットの日付/数値/テキストを通常の日付に変換します

Excel用のKutools 日付に変換 ユーティリティを使用すると、Excelでワンクリックするだけで、非標準の日付または数値(yyyymmdd)またはテキストを簡単に識別して通常の日付に変換できます。


広告を日付1に変換

数式を使用して日付を週番号に変換する

一方、WEEKNUM関数を適用して、日付を対応する週番号に変換することもできます。

1。 週番号を返す空白のセルを選択し、次の数式を入力します。 = WEEKNUM(B1,1)、Enterキーを押します。 スクリーンショットを参照してください:

注意:

(1) 上記の式では、 B1 使用する日付が含まれています。

(2) 月曜日から始まる日付から週番号を返す必要がある場合は、次の式を適用してください。 = WEEKNUM(B1,2).


関連記事:

ExcelでXNUMXつの日付の間の特定の平日の数を数える方法は?

Excelでこれまでの日/月/年を加算/減算する方法は?

最高のオフィス生産性向上ツール

人気の機能: 重複を検索、強調表示、または識別する   |  空白行を削除する   |  データを失わずに列またはセルを結合する   |   数式なしのラウンド ...
スーパールックアップ: 複数の基準の VLookup    複数の値の VLookup  |   複数のシートにわたる VLookup   |   ファジールックアップ ....
詳細ドロップダウン リスト: ドロップダウンリストを素早く作成する   |  依存関係のドロップダウン リスト   |  複数選択のドロップダウンリスト ....
列マネージャー: 特定の数の列を追加する  |  列の移動  |  Toggle 非表示列の表示ステータス  |  範囲と列の比較 ...
注目の機能: グリッドフォーカス   |  デザインビュー   |   ビッグフォーミュラバー    ワークブックとシートマネージャー   |  リソースライブラリ (自動テキスト)   |  日付ピッカー   |  ワークシートを組み合わせる   |  セルの暗号化/復号化    リストごとにメールを送信する   |  スーパーフィルター   |   特殊フィルター (太字/斜体/取り消し線をフィルター...) ...
上位 15 のツールセット12 テキスト 工具 (テキストを追加, 文字を削除する、...)   |   50+ チャート 種類 (ガントチャート、...)   |   40+ 実用的 (誕生日に基づいて年齢を計算する、...)   |   19 挿入 工具 (QRコードを挿入, パスから画像を挿入、...)   |   12 変換 工具 (数字から言葉へ, 通貨の換算、...)   |   7 マージ&スプリット 工具 (高度な結合行, 分割セル、...)   |   ... もっと

Kutools for Excel で Excel スキルを強化し、これまでにない効率を体験してください。 Kutools for Excelは、生産性を向上させ、時間を節約するための300以上の高度な機能を提供します。  最も必要な機能を入手するにはここをクリックしてください...

kteタブ201905


Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作​​業をはるかに簡単にします

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!
Comments (24)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
so much complicated.

just hit this one

=TEXT(A2 - (WEEKDAY(A2,2)) + 1, "MMM DD") & " - " & TEXT(A2 + 7 - (WEEKDAY(A2,2)), "MMM DD")
This comment was minimized by the moderator on the site
All of these formulas have issues when the date is in the last week of the year. They don't give the correct date for Monday of the last week.
This comment was minimized by the moderator on the site
I find this better "Start of week"
B5=(8-WEEKDAY(DATE(B1,1,1),1))+((B2-2)*7)+DATE(B1,1,1)

8-WEEKDAY(DATE(B1,1,1),1) => find the no. of days in a week for first week of the year
(B2-2)*7 => calculate the number of days excluding the first week of year and the week for which calculation is being done
Then add these 2 to the first day of the year to get first day of the desired week


Then "End of week",
B6=B5+6

PS:
Week starts on Sunday
For weeks starting on Monday, use this instead:
B5=(8-WEEKDAY(DATE(B1,1,1),2))+((B2-2)*7)+DATE(B1,1,1)
This comment was minimized by the moderator on the site
Your formula works perfectly for every year. End of week would be B6=B5+6 though. Thanks a lot!
This comment was minimized by the moderator on the site
Thanks. Will correct that...
This comment was minimized by the moderator on the site
This formula is overly complicated. If you have a date say 8/17/2021 in Cell A1, to get the Week Ending(as of Saturday) you just need the following: = A1-WEEKDAY(A1,1)+7This will return 8/21/2021. Date of 12/30/2020 will return 1/2/2021 as week ending.
This comment was minimized by the moderator on the site
Hi,

not sure if this has been asked, but essentially, I want to be able to drag the date and the week number automatically fill beside it when I do that.

Can anyone help?

This comment was minimized by the moderator on the site
@gilly2801 you can use an array formula for example with "=weeknum(C2:C)" press command shift enter and it will turn it into an array function.
This comment was minimized by the moderator on the site
Hi please help me.

Suppose we are considering Date 1 to 7 is week 1 and 8 to 14 is week 2. Can you please help me out how can i use if function to calculate week. I have tried but not able to get the correct result.
This comment was minimized by the moderator on the site
=ROUNDUP((TODAY()-DATE(YEAR(TODAY()),1,1))/7,0)
This comment was minimized by the moderator on the site
=ROUNDUP((TODAY()-DATE(YEAR(TODAY()),1,1))/7,0)
This comment was minimized by the moderator on the site
Thank YOU!!!!
This comment was minimized by the moderator on the site
Thank you very much!


=CONCATENATE("Inclusive Dates: ",TEXT(MAX(DATE(TEXT(TODAY(),"yyyy"),1,1),DATE(TEXT(TODAY(),"yyyy"),1,1)-WEEKDAY(DATE(TEXT(TODAY(),"yyyy"),1,1),2)+(WEEKNUM(TODAY())-1)*7+1),"MMMM")," ",TEXT(MAX(DATE(TEXT(TODAY(),"yyyy"),1,1),DATE(TEXT(TODAY(),"yyyy"),1,1)-WEEKDAY(DATE(TEXT(TODAY(),"yyyy"),1,1),2)+(WEEKNUM(TODAY())-1)*7+1),"DD"),", ",TEXT(MAX(DATE(TEXT(TODAY(),"yyyy"),1,1),DATE(TEXT(TODAY(),"yyyy"),1,1)-WEEKDAY(DATE(TEXT(TODAY(),"yyyy"),1,1),2)+(WEEKNUM(TODAY())-1)*7+1),"YYYY")," - ",TEXT(MIN(DATE(TEXT(TODAY(),"yyyy")+1,1,0),DATE(TEXT(TODAY(),"yyyy"),1,1)-WEEKDAY(DATE(TEXT(TODAY(),"yyyy"),1,1),2)+WEEKNUM(TODAY())*7),"MMMM")," ",TEXT(MIN(DATE(TEXT(TODAY(),"yyyy")+1,1,0),DATE(TEXT(TODAY(),"yyyy"),1,1)-WEEKDAY(DATE(TEXT(TODAY(),"yyyy"),1,1),2)+WEEKNUM(TODAY())*7),"DD"),", ",TEXT(MIN(DATE(TEXT(TODAY(),"yyyy")+1,1,0),DATE(TEXT(TODAY(),"yyyy"),1,1)-WEEKDAY(DATE(TEXT(TODAY(),"yyyy"),1,1),2)+WEEKNUM(TODAY())*7),"YYYY"))
This comment was minimized by the moderator on the site
Date(2017;1;7 * weeknumer - 5) 5 monday, 4 tuesday... :)
This comment was minimized by the moderator on the site
Or to make in not specific to the year 2017...
=DATE(YEAR,1,7 * WEEKNUM - WEEKDAY(DATE(YEAR,1,7) - 2))

2 monday, 3 tuesday,...
This comment was minimized by the moderator on the site
I'm sorry folks, but I could neither understand nor make work any of the formulas above so I finally figured out the following solution: DATE(B1,1,1)+($A4×7)−(6−(7−WEEKDAY(DATE(B1,1,1),first-day))) The first part "DATE(B1,1,1)+(B2×7)" simply takes January 1 of the year and adds the number of weeks. The next part calculates how many days to subtract from the WEEKDAY of January 1 to get the first day of the week. This is what I finally figured out: 6−(7−WEEKDAY(DATE(B1,1,1),first-day)) If, for example, January 1 falls on a Sunday (day 7), then this formula become "6-(7-7)" or simply 6 - which is the number of days you need to subtract to get Monday of that week. Try other days. Finally, if you want to find any other day of the week, just add the WEEKDAY number minus 1 to this result. So the last day of the week (Sunday) is: DATE(B1,1,1)+($A4×7)−(6−(7−WEEKDAY(DATE(B1,1,1),first-day)))+(7−1) Which can be simplified to: DATE(B1,1,1)+($A4×7)−(12−(7−WEEKDAY(DATE(B1,1,1),first-day))) Wednesday would be: DATE(B1,1,1)+($A4×7)−(8−(7−WEEKDAY(DATE(B1,1,1),first-day))) etc. I hope this helps someone else who needs this both with a workable solution along with a bit of understanding of how it was arrived at!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations