実行日の時間が５時半PMの前か後かによって決済日を返すエクセルの式

Question

コミュニティーの皆さま：

ご質問させていただきます。

下のように実行日(日付＋時間の値）が入ったセルのある表があるとします。

実行日の5:30PM以前のものは次の日に決済され、5:30PM以降は次の次の日に決済されるとします。

E行に実行日が5:00PM以前の値は実行日＋１日追加した日付

E行に実行日が5:00PM以後の値は実行日＋2日追加した日付

を入力できる式を入力することはできますでしょうか？

実をいうと実行日がテキストで入っているのかよくわからないので、隣にコピーして数字フォーマットを選びました。

シリアル番号が出たので、テキストでは入っていないと思うのですが・・・。

もしよろしかったらお知恵を頂けると幸いです。

Windows 7,　Excel 2013です。

Answer 1

土日を考慮した数式にするならE2セルに以下のような数式を入力します。

=WORKDAY(F2-"17:30",2)

Answer 2

マックちゃん様：

ご説明ありがとうございました。

エクセル2013を使っていてよかったです。

日数の２ですが、完全にと言うわけではないのですが、だいたいイメージで理解しようと思います。

何につまずいているかと言うと、B2は日付なのに、17:30という時刻をマイナスすると、どうしてエクセルは1日前の[開始日]を返せるのか…と言うところです。これが0:30AMだったら・・・10:30AMだったら・・・22:30PMだったら・・・やはりすべての時刻でエクセルは1日前の日付を[開始日]として返すのか？・・・これは私自身がいろいろ数字を変えて実験して研究いたします。

（インターネットに載っていた説明の一つ：

［開始日］から数えて［日数］だけ経過した日付を、土日と祭日を除外して求めます。）

そしてWORKDAYはなんと祭日まで考慮してくれるということで、練習として下のように架空で2/20/2017を祭日として引数に加えてみました。　

あとは実務で微調整するところが出てくると思いますが、基礎となっている道筋が見えてきましたのでトンネルの出口が見えてきました。　心より感謝いたします。

＞まずアドインの分析ツールを搭載していないと使えないと書いてありました。

確か、Excel2007からはアドインのチェックを入れなくても、デフォルトで使用できるようになったような経緯があります。

＞開始日にF2-"17:30"と入れられることにもびっくりしました。

日付が対象となりますので、日付+時刻のデータは時刻部分が無視されます。

また、"17:30"のような時間（や日付）の文字列を演算するとExcelが気を利かせて時刻（や日付）として計算してくれます。

＞ただ、なぜ２を選んだのかはっきりわかりませんでした。

＞平日の決済は17:30以前が翌日(+1日)、17:30以降が翌々日(+2日)です。　日数を2に指定しているのになぜ平日の決済の17:30以前の決済日の計算に影響を及ぼさないのか・・・。　

17:30以前が＋１日ということは17:30を引いた値なら前日の日付になるので＋２しなければならないので、17:30以降の日付と同じ扱いができることになります。

すなわち平日の場合は2日後の日付を求めればよいことになります。

一方、土日をまたぐ場合は、木曜なら月曜日、金曜日なら火曜日、土曜日なら火曜日のように土日を除いた日付でちょうど2日後のデータが求めたい日付になっています。

したがって、17:30を引いた日付に対して、土日を考慮した2日後の日付を求めれば、最終的にご希望の日付が求められるわけです。

＞まずアドインの分析ツールを搭載していないと使えないと書いてありました。

確か、Excel2007からはアドインのチェックを入れなくても、デフォルトで使用できるようになったような経緯があります。

＞開始日にF2-"17:30"と入れられることにもびっくりしました。

日付が対象となりますので、日付+時刻のデータは時刻部分が無視されます。

また、"17:30"のような時間（や日付）の文字列を演算するとExcelが気を利かせて時刻（や日付）として計算してくれます。

＞ただ、なぜ２を選んだのかはっきりわかりませんでした。

＞平日の決済は17:30以前が翌日(+1日)、17:30以降が翌々日(+2日)です。　日数を2に指定しているのになぜ平日の決済の17:30以前の決済日の計算に影響を及ぼさないのか・・・。　

17:30以前が＋１日ということは17:30を引いた値なら前日の日付になるので＋２しなければならないので、17:30以降の日付と同じ扱いができることになります。

すなわち平日の場合は2日後の日付を求めればよいことになります。

一方、土日をまたぐ場合は、木曜なら月曜日、金曜日なら火曜日、土曜日なら火曜日のように土日を除いた日付でちょうど2日後のデータが求めたい日付になっています。

したがって、17:30を引いた日付に対して、土日を考慮した2日後の日付を求めれば、最終的にご希望の日付が求められるわけです。

Answer 3

＞まずアドインの分析ツールを搭載していないと使えないと書いてありました。

確か、Excel2007からはアドインのチェックを入れなくても、デフォルトで使用できるようになったような経緯があります。

＞開始日にF2-"17:30"と入れられることにもびっくりしました。

日付が対象となりますので、日付+時刻のデータは時刻部分が無視されます。

また、"17:30"のような時間（や日付）の文字列を演算するとExcelが気を利かせて時刻（や日付）として計算してくれます。

＞ただ、なぜ２を選んだのかはっきりわかりませんでした。

＞平日の決済は17:30以前が翌日(+1日)、17:30以降が翌々日(+2日)です。　日数を2に指定しているのになぜ平日の決済の17:30以前の決済日の計算に影響を及ぼさないのか・・・。　

17:30以前が＋１日ということは17:30を引いた値なら前日の日付になるので＋２しなければならないので、17:30以降の日付と同じ扱いができることになります。

すなわち平日の場合は2日後の日付を求めればよいことになります。

一方、土日をまたぐ場合は、木曜なら月曜日、金曜日なら火曜日、土曜日なら火曜日のように土日を除いた日付でちょうど2日後のデータが求めたい日付になっています。

したがって、17:30を引いた日付に対して、土日を考慮した2日後の日付を求めれば、最終的にご希望の日付が求められるわけです。

Answer 4

TETUO様：

マクロをありがとうございました。

とても助かります。

決済日の設定から始まり、その上に特別なスタイル・フォーマット・ピボットテーブルなどを追加するにあたって、別の条件により分岐・繰り返し処理が必要になることが必ず起こってくると思います。

コードがあると非常に心強い地図になりますので感謝です。

何の工夫もないマクロで恥ずかしいのですが、、、

Sub 決済日入力マクロ()

    Dim i As Long

    Dim last As Long

    last = Cells(Rows.Count, 6).End(xlUp).Row

    For i = 2 To last

        If Weekday(Cells(i, 6)) = vbMonday And TimeValue(Cells(i, 6)) <= TimeValue("17:30") Then

            Cells(i, 5) = Cells(i, 6) + 1

        ElseIf Weekday(Cells(i, 6)) = vbMonday And TimeValue(Cells(i, 6)) > TimeValue("17:30") Then

            Cells(i, 5) = Cells(i, 6) + 2

        ElseIf Weekday(Cells(i, 6)) = vbTuesday And TimeValue(Cells(i, 6)) <= TimeValue("17:30") Then

            Cells(i, 5) = Cells(i, 6) + 1

        ElseIf Weekday(Cells(i, 6)) = vbTuesday And TimeValue(Cells(i, 6)) > TimeValue("17:30") Then

            Cells(i, 5) = Cells(i, 6) + 2

        ElseIf Weekday(Cells(i, 6)) = vbWednesday And TimeValue(Cells(i, 6)) <= TimeValue("17:30") Then

            Cells(i, 5) = Cells(i, 6) + 1

        ElseIf Weekday(Cells(i, 6)) = vbWednesday And TimeValue(Cells(i, 6)) > TimeValue("17:30") Then

            Cells(i, 5) = Cells(i, 6) + 2

        ElseIf Weekday(Cells(i, 6)) = vbThursday And TimeValue(Cells(i, 6)) <= TimeValue("17:30") Then

            Cells(i, 5) = Cells(i, 6) + 1

        ElseIf Weekday(Cells(i, 6)) = vbThursday And TimeValue(Cells(i, 6)) > TimeValue("17:30") Then

            Cells(i, 5) = Cells(i, 6) + 4

        ElseIf Weekday(Cells(i, 6)) = vbFriday And TimeValue(Cells(i, 6)) <= TimeValue("17:30") Then

            Cells(i, 5) = Cells(i, 6) + 3

        ElseIf Weekday(Cells(i, 6)) = vbFriday And TimeValue(Cells(i, 6)) > TimeValue("17:30") Then

            Cells(i, 5) = Cells(i, 6) + 4

        ElseIf Weekday(Cells(i, 6)) = vbSaturday Then

            Cells(i, 5) = Cells(i, 6) + 3

        ElseIf Weekday(Cells(i, 6)) = vbSunday Then

            Cells(i, 5) = Cells(i, 6) + 2

        End If

    Next

End Sub

Answer 5

マックちゃん様：

引き続きのご質問をお許しください。

WORKDAYS関数について調べてみました。

まずアドインの分析ツールを搭載していないと使えないと書いてありました。

わたしは何もしていないのにいきなり使えたことにびっくりしました。

一言で説明すると『土日なら後の平日を返す』という感じなのでしょうか。

開始日にF2-"17:30"と入れられることにもびっくりしました。

まるで文章に書いた言葉をエクセルが理解する・・・という感覚ですね。

そしてこの"17:30"は自由に時刻を変更できる柔軟さがあるということがわかりました。

次の日数でちょっとつまずきました。

日数    必ず指定します。 開始日から起算して、週末や祭日を除く週日の日数を指定します。 日数に正の数を指定すると、起算日より後の日付となり、負の数を指定すると、起算日より前の日付となります。

頂いた式の日数は正の数ですので起算日より後ということになります。これはもちろんそうなるべきだと思いました。売り上げもないのに決済は起こりません・・・。ただ、なぜ２を選んだのかはっきりわかりませんでした。　

それは土曜日と日曜日を合計すると単純に２日間だから２にするのか・・・。じゃあ、決済会社が土曜日・日曜日・月曜日の３日間お休みだとすると3を選ぶのか・・・。でもそうすると平日の計算に全く影響がないのか？が疑問に思いました。　

平日の決済は17:30以前が翌日(+1日)、17:30以降が翌々日(+2日)です。　日数を2に指定しているのになぜ平日の決済の17:30以前の決済日の計算に影響を及ぼさないのか・・・。　式が優秀すぎてそれがちょっとわかりませんでした。

２は…何を意味しているのでしょうか？

土日を考慮した数式にするならE2セルに以下のような数式を入力します。

=WORKDAY(F2-"17:30",2)