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

Excelで日付を前または次の特定の平日に丸める方法は?

doc-round-to-previous-sunday-1
場合によっては、左のスクリーンショットに示すように、日付に基づいて日曜日などの前または次の特定の平日を取得する必要があります。 ここで、このチュートリアルは、Excelでこのジョブを簡単に処理するためのXNUMXつの式を提供します。
次の特定の平日に丸める日付
前の特定の平日に丸める日付

矢印青い右バブル 次の特定の平日への丸め日

たとえば、ここでは、列Aの日付の次の日曜日を取得します

1.次の日曜日に配置するセルを選択し、数式を貼り付けるか入力します。

=IF(MOD(A2-1,7)>7,A2+7-MOD(A2-1,7)+7,A2+7-MOD(A2-1,7))

2.次にを押します 入力します キーを押して次の最初の日曜日を取得します。これは5桁の数字として表示され、自動入力を下にドラッグしてすべての結果を取得します。

doc-round-to-previous-sunday-1

3.次に、数式セルを選択したまま、を押します Ctrl + 1 表示するキー セルの書式設定 ダイアログ、次に下 タブ、選択 日付 必要に応じて、右側のリストからXNUMXつの日付タイプを選択します。 クリック OK.

doc-round-to-previous-sunday-1

これで、数式の結果が日付形式で表示されました。

doc-round-to-previous-sunday-1

次の平日を取得するには、以下の式を使用してください。

平日
日曜日 =IF(MOD(A2-1,7)>7,A2+7-MOD(A2-1,7)+7,A2+7-MOD(A2-1,7))
土曜日 =IF(MOD(A2-1,7)>6,A2+6-MOD(A2-1,7)+7,A2+6-MOD(A2-1,7))
金曜日 =IF(MOD(A2-1,7)>5,A2+5-MOD(A2-1,7)+7,A2+5-MOD(A2-1,7))
木曜日 =IF(MOD(A2-1,7)>4,A2+4-MOD(A2-1,7)+7,A2+4-MOD(A2-1,7))
Wednesday =IF(MOD(A1-1,7)>3,A1+3-MOD(A1-1,7)+7,A1+3-MOD(A1-1,7))
;火曜日 =IF(MOD(A1-1,7)>2,A1+2-MOD(A1-1,7)+7,A1+2-MOD(A1-1,7))
月曜日 =IF(MOD(A1-1,7)>1,A1+1-MOD(A1-1,7)+7,A1+1-MOD(A1-1,7))

矢印青い右バブル 前の特定の平日への丸め日

たとえば、ここで列Aの日付の前の日曜日を取得します

1.次の日曜日に配置するセルを選択し、数式を貼り付けるか入力します。

= A2-WEEKDAY(A2,2)

2.次にを押します 入力します キーを押して次の最初の日曜日を取得し、自動入力を下にドラッグしてすべての結果を取得します。

doc-round-to-previous-sunday-1

日付形式を変更する場合は、数式セルを選択したままにして、を押します。 Ctrl + 1 表示するキー セルの書式設定 ダイアログ、次に下 タブ、選択 日付 必要に応じて、右側のリストからXNUMXつの日付タイプを選択します。 クリック OK.

doc-round-to-previous-sunday-1

これで、数式の結果が日付形式で表示されました。

doc-round-to-previous-sunday-1

他の平日を取得するには、以下の式を使用してください。

平日
日曜日 = A2-WEEKDAY(A2,2)
土曜日 =IF(WEEKDAY(A2,2)>6,A2-WEEKDAY(A2,1),A2-WEEKDAY(A2,2)-1)
金曜日 =IF(WEEKDAY(A2,2)>5,A2-WEEKDAY(A2,2)+5,A2-WEEKDAY(A2,2)-2)
木曜日 =IF(WEEKDAY(A2,2)>4,A2-WEEKDAY(A2,2)+4,A2-WEEKDAY(A2,2)-3)
Wednesday =IF(WEEKDAY(A2,2)>3,A2-WEEKDAY(A2,2)+3,A2-WEEKDAY(A2,2)-4)
;火曜日 =IF(WEEKDAY(A2,2)>2,A2-WEEKDAY(A2,2)+2,A2-WEEKDAY(A2,2)-5)
月曜日 =IF(WEEKDAY(A2,2)>1,A2-WEEKDAY(A2,2)+1,A2-WEEKDAY(A2,2)-6)

強力な日付と時刻のヘルパー

  日付と時刻のヘルパー の特徴 Kutools for Excel、簡単に日時の加算/減算、XNUMXつの日付の差の計算、誕生日に基づく年齢の計算をサポートします。  クリックして無料トライアル!
doc-round-to-previous-sunday-1
 
Kutools for Excel:200を超える便利なExcelアドインがあり、制限なしで自由に試すことができます。

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

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

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

説明


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

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

=IF(A2="","",A2+5-Mod(A2-1,7))

I have entered 11/19/2022
The result was past day 11/18/2022
This comment was minimized by the moderator on the site
Thanks for your reminder, the original formula is not enough rigorous indeed. I have updated the formulas and rewrited the tutorial, hope the new formulas can help you.
This comment was minimized by the moderator on the site
Im trying to work out how to use a date, less an amount of days but ensure when subtracting the days it lands on a weekday not a weekend.
Any formulas??
This comment was minimized by the moderator on the site
Hi Everyone,
I use the formula below to round to the next Thursday where A1 is the cell with your original date and 14 is for Thursday. You can use other numbers for other days if required. 11=Monday, 12=Tuesday, 13=Wednesday, etc...

Also, you can use a 1 instead of an 8 to get the Thursday before your original date in A1. If your original date is already a Thursday the formula will produce the same date as in A1.

I hope this helps!

=A1+(0-WEEKDAY(A1,14)+8)
This comment was minimized by the moderator on the site
Thank you! This worked :)
This comment was minimized by the moderator on the site
I put this into google sheets and to make the dates become next thursday but I have not been able to get it to work especially for the dates right after thursdays. =IF(L2="","",L2+4-MOD(L2-1,4)) is the function i used to make it thursday but not all dates were thursday.
This comment was minimized by the moderator on the site
to clarify, all dates become thursday, but friday and saturday will not convert to the next weeks thursday instead they will go back a day or two to the previous thursday instead going to next weeks thursday, is there any way to fix this in the function =IF(L4="","",L4+4-MOD(L4-1,7))
This comment was minimized by the moderator on the site
Hi Kisho,

I use the formula below to round to the next Thursday where A1 is the cell with your original date and 14 is for Thursday. You can use other numbers for other days if required. 11=Monday, 12=Tuesday, 13=Wednesday, etc...

Also, you can use a 1 instead of an 8 to get the Thursday before your original date in A1. If your original date is already a Thursday the formula will produce the same date as in A1.

I hope this helps!

=A1+(0-WEEKDAY(A1,14)+8)
This comment was minimized by the moderator on the site
Hi Kisho,
I use the formula below to round to the next Thursday where A1 is the cell with your original date and 14 is for Thursday. You can use other numbers for other days if required. 11=Monday, 12=Tuesday, 13=Wednesday, etc...
Also, you can use a 1 instead of an 8 to get the Thursday before your original date in A1. If your original date is already a Thursday the formula will produce the same date as in A1.
I hope this helps!
=A1+(0-WEEKDAY(A1,14)+8)
This comment was minimized by the moderator on the site
I put this into google sheets and to make the dates become next thursday but I have not been able to get it to work especially for the dates right after thursdays. =IF(L2="","",L2+4-MOD(L2-1,4)) is the function i used to make it thursday but not all dates were thursday.
This comment was minimized by the moderator on the site
Si quiero la fecha del próximo viernes cómo debo modificar las variables? Gracias
This comment was minimized by the moderator on the site
Sorry, I cannot understand, could you speak in English?
This comment was minimized by the moderator on the site
hi guys,

I used the formula below, I hope it works for you.
IF(WEEKDAY(H2,16)<>7,(7-WEEKDAY(H2,16))+H2,H2) where H2 is the date that you want to round up to the next day of the week (in my case for Friday) and the 16 is the format to start the date with Saturday as #1 and Friday as #7.
This comment was minimized by the moderator on the site
Your round to previous Sunday is flawed. Even shows as much on your sheet.
This comment was minimized by the moderator on the site
So, what would be the correct formula to first check to see if a cell has a date, else leave it blank and make the date the fall on a Friday before if the date falls on a Saturday or Sunday?
This comment was minimized by the moderator on the site
I found a workaround for to the previous Friday. You can change the switch "results" as needed to round to different days.=H3-SWITCH(TEXT(H3, "DDD"), "Mon", 3, "Tue", 4, "Wed", 5, "Thu", 6, "Fri", 0, "Sat", 1, "Sun", 2)
=[date]-SWITCH(TEXT([date], "DDD"), "Mon", 3, "Tue", 4, "Wed", 5, "Thu", 6, "Fri", 0, "Sat", 1, "Sun", 2)
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations