１日＝465分の勤務時間の合計計算

質問

お世話になります。
1日7時間45分勤務において、従事時間の合計を日数・時間・分で表示させたいのですが、
以下のような数式を入れて、時間計算しようとしたのですが、どうしてもうまくいきません。

例）従事時間合計が25時間15分の場合、3日2時間0分と表示させたい。

＝SUM()で、表示形式を[h]:mmにして、合計従事時間数「25：15（セルA2)」を算出。
次に、総取得分にする・・・B2＝A2＊24＊60＝1515（以下表示形式はすべて：標準）
　　　　総取得分を日（465分）に変換する・・・C2=ROUNDDOWN(B2/465,0)
　　　　日数を除き残った分を計算する・・・D2=B2-C2*465
　　時間に変換・・・E1=D1/60
　　　　残分を計算・・・F1=D2-ROUNDDOWN(E2,0)*60
という、式を組み立てました。

表示させるセル　D5=C2 (表示形式　＃"日"）
E5=ROUNDDOWN(E2,0) (表示形式　＃"時間"）
　　　　　　　　　　　F5=F2　(表示形式　＃"分"）

　とすると、3日1時間60分となってしまいます。　　・・・3日2時間の表示にしたい。
従事時間合計が55時間15分の場合も、7日60分という表示になってしまいます。　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　・・・7日と1時間にしたい。

よい方法はありませんでしょうか？
よろしくお願いいたします。

回答No.4

mshr1962 · ベストアンサー

No.3です。No.2の方のをみて気付きました。
B2=TEXT(A2,"[m]")*1
または
B2=ROUND(A2*24*60,0)
としてください。
C2以降は現在のままでもNo.3で紹介した数式でもOKです。

■計算の齟齬の原因
A2の時間合計が25:15と表示され、B2が1515となってますが
実際は、1514.9999・・・なのだと思います。
ここを直すことで後の計算が正常になります。

下記の数式が、今回の結果がおかしくなった原因ですね。
時間に変換・・・E1=D1/60
残分を計算・・・F1=D2-ROUNDDOWN(E2,0)*60
↓
時間に変換・・・E2=ROUNDDOWN(D2/60,0)
残分を計算・・・F2=D2-E2*60
としておけば　E2=1,F2=60（実際は59.999・・・）となって
D2（元はB2ですが）が繰り上げで120（実際は119.999・・・）だったのが分かると思います。

お礼

丁寧なご指導ありがとうございました。B2で端数処理をすることにより、確かに解決しました。
　残120分なのに、2時間と60分と表示されるのも謎だったので、一緒に解説していただき、大変助かりました。とても勉強になりました。ありがとうございました。

回答No.3

mshr1962

下記の数式を置き換えてみてください。
C2=QUOTIENT(B2,465)
D2=MOD(B2,465)
E2=QUOTIENT(D2,60)
F2=MOD(D2,60)

お礼

従事時間合計をその他の方法で日・時間・分で表す方法を教えていただき、ありがどうございます。勉強になります。

回答No.2

MackyNo1

ご質問の現象は、小数点以下の計算における丸め誤差によって発生しています。

すなわち、時間のような小数点以下の数字の加減乗除（特に引き算）を行なうと、セルの表示は「25：15」でも実際は、手入力した値と異なり、それよりわずかに小さいあるいは大きい値になっていることがあります（たとえば手入力したA1セルと数式で得たA2セルを比較するために「＝A1-A2>0」のような数式を入力するとTRUEが返ります）。

このようなセルを参照して「～以下」の条件やROWNDDOWN関数などの切り捨てを行うと、今回のように正しい値が返されないことになります。

このような場合は微小値を加減した数式にするなどの工夫が必要となります（今回の切り捨て処理なら微小値を加える）。

=A2セルの数式+10^-10

しかし、今回のような整数化できるケースでは、数式で計算したデータを手入力した値と同じデータに変換する方法がもっとも簡便な対応です。

すなわち、A2セルの数式を以下のように変更してください。

=TEXT(A1の計算式,[h]:mm)*1

お礼

ご指導ありがとうございます。数式を組み立てる途中で、何度か表示と異なる結果がでて困ったので、小数点以下の計算における丸め誤差の解説で、とても勉強になりました。ありがとうございました。

回答No.1

keithin

従事時間合計に表示では現れていない端数の時間数が含まれているのが原因と思われます。
いろんな所で頑張ってROUNDDOWN等を使っていますが，端数を適切に処理できていません。

とりあえずB2の式を
=ROUND(A2*24*60,0)
とすれば，ほぼ解決すると思います。

また
１．各計算セルの表示形式を「標準」または「小数点以下の桁数が沢山表示」するようにして，どこに端数が残っているか，よく確認してください
２．「全ての計算式」で，漏れなくROUND系の関数を使ってください。

お礼

ご指導ありがとうございます。B2で端数処理をすることにより、確かに解決しました。端数を適切に処理できるように、ROUND系の関数を使用する場合は、注意したいと思います。ありがとうございました。