☰

2日間にまたがる期間を計算する式

tag-icon • tag-icon microsoft-excel worksheet-function
2日間にまたがる期間を計算する式

列A: 24時間形式の開始時刻(数式を使用し、 + +=NOW()でロック) 列: 24時間形式の終了時刻(列 と同じものを入力) 列:数式を使用してとしてフォーマットされた期間CtrlSHIFT;
BA
C[mm]=B1-A1

列のデータにB翌日に発生する時刻が含まれている場合、期間をどのように計算すればよいですか?

実際のワークシート:

実際の作業のスクリーンショット

答え1

おそらく最もエレガントでシンプルな解決策は、この答えfixer1234 が言及このコメント:

=MOD(B1-A1,1)

両方の値が時間値のみである（または日付部分が切り捨てられる）こと、つまりそれぞれが 1 未満であり、期間が 24 時間未満であることを前提としていることに注意してください。

説明：

これが機能するのは、シリアル日付時刻値の場合、1 は 24 時間に対応し、時間のみの値は 1 未満の日付時刻値であるためです。つまり、

  • すると→予想通りB1 >= A1、 またはMOD(B1-A1,1)B1-A1
  • ならばB1 < A1、は（ なので）MOD(B1-A1,1)と等しくなり、正しい「補数」期間を与える。1-(A1-B1)MOD(-n,m)=m-MOD(n,m)


代替の解決策（よりシンプル/エレガントかどうかはあなたが判断してください ;-) ）は次のとおりです。

=(B1<A1)+B1-A1

説明：

これが機能するのは、算術演算で使用されるときにTRUE値が暗黙的に に強制変換され1、FALSE値が に強制変換されるためです。つまり、0

  • すると→B1 >= A1となり、式は または(B1<A1)0B1-A1
  • ならば→B1 < A1であり、式は次のようになる。(B1<A1)11-(A1-B1)

この式は正しく機能することに注意してください両方の値が日付時刻であってもそして24時間以上離れています!これはない最初の式の場合、この 2 番目の式の方がより有用で一般的な式になります。

答え2

Ctrl+ Shift+ は;日付スタンプなしで現在の時刻をセルに入力するだけであり、@Akina が回答で指摘しているように、何もロックされません。

NOW()セルに日付スタンプと時刻が追加されます。それを確認するには、General書式設定されていない空のセルまたは書式設定されたセルと入力します=NOW()。これを入力している時点では、 が表示されます21/08/2018 11:25。

=NOW()セルにA1とを入力し、次にセルに を=NOW()+TIME(23,0,0)入力してセルを に書式設定すると、が得られます。B1=B1-A1C1Time23:00:00

日付スタンプなしで時間を入力する場合は、IF()数式を使用して、2 番目の時間が最初の時間よりも短いかどうか (時間枠が深夜を過ぎているかどうか) を判断する必要があります。次の式は、終了時間が深夜を過ぎているかどうかに関係なく、セルB1とセルの時間の差を計算しますA1。日付スタンプは無視されるため、日付スタンプの有無に関係なく機能します。

=IF(B1<A1,MOD(B1-A1,1),B1-A1)

式の説明

=IF(B1<A1,...)

セルの時刻はB1深夜を過ぎていますか？もしそうなら、次の部分が実行されます

MOD(B1-A1,1)

MOD(number,divisor)は、数値を除数で割った後の余りを返します。セルからB1セルを引くと、A1セルの時刻がB1午前 0 時を過ぎているため負の値になりますが、Excel はほとんどの場合、負の時刻値を認識しないため、 なし#####で返されますMOD()。 はMOD()これを整理します。

MOD()この式の代わりに、

TIME(23,59,59)-TIME(HOUR(A1),MINUTE(A1),SECOND(A1))+TIME(0,0,1)+B1

それは です23:59:59 minus time in cell A1 plus 1 second plus time in cell B1。

最後のビットは、,B1-A1)セルIF()内の時間B1がない真夜中過ぎ。

答え3

@Akina さん、ありがとうございます。これは私にとって唯一うまくいった数式です... 上記のいくつかを試してみましたが (ただし、正の投票数で)、恐ろしい ###### または小数点がいくつか付いた負の数になりました。日付スタンプやその他の意味不明な説明なしで、2 日間にまたがる時間を計算しました...

答え4

Minutes2 つの間の差を取得するには、24Hrs DateTime Stamps次のいずれかを使用できます。

=Int((A2-A1)*24*60)

または

=int((A2-A1)*1440)

または

=ROUNDUP((A2-A1)*1440,2)

注記、数式セルにはGeneral書式が必要です。

関連情報