実行日の時間が５時半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

6:30を足してやってそのあと書式なり切り捨てなり

つかえばよさそう。

ファ～様：

アドバイスありがとうございました。

書式をいろいろ工夫して正しい日付が返るように研究してみます。

Answer 3

E2に =IF(TIME(HOUR(F2),MINUTE(F2),0)<=TIMEVALUE("17:30"),F2+1,F2+2)

と入力して表示形式は適当に。

TETUO様：

ありがとうございました！！

一番下に貼ってある通りきれいにできました。

・・・ということはF列に入っていたのはテキストではなく、本当の日付と時間が連結された値だったんですね。　長い間エクセルを使っていますが、一つのセルに日付と時間の両方の値を入れられることを知りませんでした。このレポートはわたしが作ったのではなく外から与えられるレポートです。

今しばしこの質問を開けて置いたままにしてよろしいでしょうか？

実はこの作業に一つ厄介な点があって、それは『週末』なんです。

決済が行われるのは月・火・水・木・金のみです。

月曜日に決済されるのは：　木曜日5:30PM~金曜日5:30PM以前の実行日分の売上が月曜日に決済される。

火曜日に決済されるのは

金曜日の5:30PM以降の売上が火曜日に決済される＝決済日＝金曜日実行日＋４日追加した日付

土曜日の全売上げが火曜日に決済される＝決済日＝土曜日実行日＋3日追加した日付

日曜日の全売上げが火曜日に決済される＝決済日＝日曜日実行日＋2日追加した日付

月曜日の実行日分の売り上げの5:30PM前の売り上げが＋1日追加した　火曜日に決済される。

水曜日に決済されるのは：　月曜日5:30PM~火曜日5:30PMまでの実行日分の売り上げが水曜日に決済される。

木曜日に決済されるのは：　火曜日5:30PM~水曜日5:30PMまでの実行日分の売り上げが木曜日に決済される。

金曜日に決済されるのは：　水曜日5:30PM~木曜日5:30PMまでの実行日分の売り上げが金曜日に決済される。

一日の始まりは夜中の12時なのだから決済もその通りに進んでくれればいいのに、決済ｻｲｸﾙは5:30~5:30

締め切りと決められてしまっています。一日の売り上げが5:30PMを境に２つにブチ切られて別々の日に決済されるので一日の売上合計＝決済額でないので分析が非常に大変です。

TETUO様に頂いた式を使って火曜日の決済の日付だけ手作業で日付を変えてもいいのですが、最終的には正しい決済日をE列に入力してその日付をもとにピボットテーブルを作成し、売上表データベースを決済額グループに変換する・・・というのがゴールです。

もし、5:30~5:30締め切りに対応できるエクセルの仕組みを確立したらあとは締め切りが何時になろうと同じゴールを達成できます。　その仕組みは作業の中であまりにも普遍的な作業なので今回頑張って作ってみようと思いました。

TETUO様、式を本当にありがとうございました。わたしには決して組み立てられない複雑な式でした。心より感謝いたします。　またこれから作業を続けます。

Answer 4

6:30を足してやってそのあと書式なり切り捨てなり

つかえばよさそう。

Answer 5

E2に =IF(TIME(HOUR(F2),MINUTE(F2),0)<=TIMEVALUE("17:30"),F2+1,F2+2)

と入力して表示形式は適当に。