EXCELでの時間計算について

質問

私の会社には、1ヶ月の残業時間を入力し印刷して提出する為のEXCELデータがあります。
自分で日毎に残業時間を入力して、その合計を電卓で計算して入力するようなものです。
残業した日が多い時は、いちいち合計を計算するのが面倒なので、なにか良い計算式は無いでしょうか。
今入力している方法は、A列に時間、B列に分（10分単位の入力）という感じで1ヶ月分を入力しています。
A列の「時間」は単純に足していけばよいのでしょうが、B列は「分」なので例えば合計して510分となったとき、
それを8時間30分にして、その8時間をA列のほうに足すというような計算式を教えて頂けませんでしょうか。
あと、日毎の残業時間は10分単位で入力するのですが、その月の合計は30分単位にして提出しなければなりません。
例えば、合計が30時間40分～50分の場合は30時間30分にしなければなりませんし、
30時間10分～20分の場合は30時間00分にしなければなりません。
この両方の内容を兼ね備えた計算ができる方法を教えて頂きたく存じます。
分かりにくい文章で、申し訳ありませんがよろしくお願い致します。

回答No.8

hallo-2007

ちょっと心配になって
A列もB列も数値なのですよね
分は30未満は0　30～59は　30ですよね
時間の合計(仮にC1)　=SUM(A:A)+INT(SUM(B:B)/60)
＊分の合計を60で割って、整数の部分を足す
分の合計(仮にD1)　　=IF(MOD(SUM(B:B),60)<30,0,30)
＊分の合計を60で割ったあまりが30より小さい 0 他は30
では如何でしょうか？

回答No.7

tossy005

補足
すみません。間違いがありましたので訂正致します。
時間の合計のセルがD6、分の合計のセルがE6としたとき、
以下のものを回答を表示したいセルにコピー＆ペーストしてください。
=D6+INT(FLOOR(CONVERT(E6,"mn","hr"),0.5)) & "時間" & (FLOOR(CONVERT(E6,"mn","hr"),0.5)-INT(FLOOR(CONVERT(E6,"mn","hr"),0.5)))*60 & "分"

たびたびの連続投稿大変失礼致しました。

回答No.6

tossy005

すみません。時間も分も合計がすでに出ているのですね。
時間の合計のセルをＤ６、分の合計のセルをＥ６とすると
答えを出したいセルに以下の内容をコピー＆ペーストします。
=D6+INT(FLOOR(CONVERT(E6,"mn","hr"),0.5)) & "時間" & FLOOR(CONVERT(E6,"mn","hr"),0.5)-INT(FLOOR(CONVERT(E6,"mn","hr"),0.5))& "分"
これで●●時間○分と表示されます。
ただし、Ｄ６、Ｅ６に「●●時間」「○○分」のように
"時間"、"分"と書いてある場合は使用できませんので
前のアドバイスのように１セルずつ計算したほうがよいと思います。

回答No.5

tossy005

Ａ２～Ａ３０に時間、Ｂ２～Ｂ３０に分が書いてあるとすると

(1)Ｃ２に以下のものをコピー＆ペーストします
=convert("A2","hr","mn") + B2
これでC2にA2の時間を分に変換したものとB2の分の合計が出ます

(2)Ｃ３～Ｃ３０にＣ２のデータをコピー＆ペーストします

(3)以下のものを適当なところにコピー＆ペーストします
=Floor(convert(SUM(C2:C30),"mn","hr"),0.5)
ペーストしたセルに残業時間の合計が３０分単位で丸まった形で出ます。
ただし、以下の作業が必要になります。
たとえば、残業時間の合計が72時間30分なら「72.5」と表示されるので
手動で「72時間30分」と書き換えてください。

回答No.4

Wendy02

こんにちは。

こんな式かな？

A1:B30 まであるとしたら、

=FLOOR(SUMPRODUCT(TIME(A1:A30,B1:B30,)),"0:30")
これだけで出ます。

なお、表示は、書式形式を、ユーザー設定で、
　[h]:mm
とします。

これで、検算してみてください。
-----------------------------------------
本来は、
>A列に時間、B列に分（10分単位の入力）

これを別の空いている列（補助列）を使って、
　=TIME(A1,B1,0)

または、10分ごとなら、

=FLOOR(TIME(A1,B1,),"0:10")

という数式を入れて、その合計を、上記のように、FLOOR関数で丸めてあげれば簡単だったはずです。

テキスト型なら、= TEXT(集計,"[h]:mm") になります。
ただし、時間と分を分けるのは、

補助列を、C列に使った場合
時間：
=TEXT(C30,"[h]")
分：
=SUBSTITUTE(TEXT(C30,"[h]:mm"),A30&":","")
このようにすると簡単です。

回答No.3

hige_082

初心者にはこの辺りが分かりやすいかと
合計は求めることが出来るとして

=int(合計/60)&"時間"&if(mod(合計,60)<30,0,30)&"分"

解説
int()は小数点以下を切り捨てる(整数に丸める)関数
mod()は余りを求める関数

回答No.2

hallo-2007

A列もB列も数値なのですよね
時間の合計(仮にC1)　=SUM(A:A)+INT(SUM(B:B)/60)
＊分の合計を60で割って、整数の部分を足す
分の合計(仮にD1)　　=MOD(SUM(B:B),60)
＊分の合計を60で割ったあまり
では如何でしょうか？

>合計が30時間40分～50分の場合は30時間30分にしなければなりません
>30時間10分～20分の場合は30時間00分にしなければなりません。
30時間21分～39分の場合は?　30時間51分～9分の場合は?

仮に　20分未満は　0 50分未満は　30分　50以上は1時間
C2に　=IF(D1<50,C1,C1+1)
D2に　=IF(D1<20,0,IF(D1<50,30,0))
とIF文で組み合わせ考えてみてください。

回答No.1

mshr1962

=TEXT(FLOOR(SUM(時間の範囲)*60+SUM(分の範囲),30)/1440,"[h]時間mm分")

解説
（１）時間の合計を60倍して分に置き換える。
（２）分の合計に（１）を加算する。
（３）FLOOR関数で30分単位に切捨てを行なう。
（４）1440(24時間*60分)で除算して時刻のシリアル値に変換
（５）TEXT関数で時間+分表示

補足

早速のご回答ありがとうございます。
私の説明が不十分だったようですので追加致します。
再度ご回答頂けますと大変助かります。

EXCELの合計欄も、A列「時間」とB列「分」に分かれています。
ですので、それぞれに計算式が必要になります。
B列を合計して○時間○分にし、○時間の部分をA列に足す。
さらに30分単位の切捨てを行う。
そういうことは可能でしょうか。
お手数をお掛けしますが、よろしくお願い致します。