• 締切済み

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分にしなければなりません。 この両方の内容を兼ね備えた計算ができる方法を教えて頂きたく存じます。 分かりにくい文章で、申し訳ありませんがよろしくお願い致します。

みんなの回答

  • hallo-2007
  • ベストアンサー率41% (888/2115)
回答No.8

ちょっと心配になって 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 では如何でしょうか?

  • tossy005
  • ベストアンサー率38% (7/18)
回答No.7

補足 すみません。間違いがありましたので訂正致します。 時間の合計のセルが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 & "分" たびたびの連続投稿大変失礼致しました。

  • tossy005
  • ベストアンサー率38% (7/18)
回答No.6

すみません。時間も分も合計がすでに出ているのですね。 時間の合計のセルを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))& "分" これで●●時間○分と表示されます。 ただし、D6、E6に「●●時間」「○○分」のように "時間"、"分"と書いてある場合は使用できませんので 前のアドバイスのように1セルずつ計算したほうがよいと思います。

  • tossy005
  • ベストアンサー率38% (7/18)
回答No.5

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

  • Wendy02
  • ベストアンサー率57% (3570/6232)
回答No.4

こんにちは。 こんな式かな? 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&":","") このようにすると簡単です。

  • hige_082
  • ベストアンサー率50% (379/747)
回答No.3

初心者にはこの辺りが分かりやすいかと 合計は求めることが出来るとして =int(合計/60)&"時間"&if(mod(合計,60)<30,0,30)&"分" 解説 int()は小数点以下を切り捨てる(整数に丸める)関数 mod()は余りを求める関数

  • hallo-2007
  • ベストアンサー率41% (888/2115)
回答No.2

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文で組み合わせ考えてみてください。

  • mshr1962
  • ベストアンサー率39% (7418/18948)
回答No.1

=TEXT(FLOOR(SUM(時間の範囲)*60+SUM(分の範囲),30)/1440,"[h]時間mm分") 解説 (1)時間の合計を60倍して分に置き換える。 (2)分の合計に(1)を加算する。 (3)FLOOR関数で30分単位に切捨てを行なう。 (4)1440(24時間*60分)で除算して時刻のシリアル値に変換 (5)TEXT関数で時間+分表示

great_tree
質問者

補足

早速のご回答ありがとうございます。 私の説明が不十分だったようですので追加致します。 再度ご回答頂けますと大変助かります。 EXCELの合計欄も、A列「時間」とB列「分」に分かれています。 ですので、それぞれに計算式が必要になります。 B列を合計して○時間○分にし、○時間の部分をA列に足す。 さらに30分単位の切捨てを行う。 そういうことは可能でしょうか。 お手数をお掛けしますが、よろしくお願い致します。

関連するQ&A

  • エクセル 残業時間計算・表示

    日々の残業時間を、A列に時間、B列に10分単位で入力しています。 月末には、B列の合計を30分単位で丸めます。 例)  A B  1  1 20  ・  2 30  ・  1 50 30  0 40 合計  6 00 分を集計すると2時間20分になります。 合計行の分の表記は30分単位で丸めたいので、 30分未満は00、30分以上は30と表記したいのですが、 どのように、計算(関数)処理すれば出来るのでしょうか。 毎月のことなので、自動計算出来れば助かるのですが。

  • エクセルと時間の計算

    エクセルと時間の計算 学校勤務で勤務時間計算の事務を以下のようなエクセルシートでしています。 表の上段は、残業のない通常の場合ですが、下段は、残業があった場合の シートになっています。 Aさんは8時間勤務で休憩45分、Bさんは4時間勤務で休憩なしです。 それ以上は残業になるのですが、下段の表がその場合です。 ここで、通常の勤務時間合計と残業時間合計を分けて計算したいです。 その結果を F列6,7とG列6,7に表示したいのですが。 Aさんは8時間を超えた時間は、G6の合計に、 Bさんは4時間を超えた時間は、G7の合計に 入れたいのですが、どんな数式を入れればいいのでしょうか。 関数初心者で申し訳ありませんが、よろしくお願いします。

  • 残業時間計算について

    色々既出してあると思いますが、 分かる方ご教授お願い致します。 17:30までに提出の為焦っています。 お助け下さい><   A(終了時間) B(残業時間)     1 19:47        2:17 2 17:44        0:00 3 18:37        1:07 4          計 3:24   B列の残業時間を終了時間入力したら 勝手に出るようにしたいのです。 18:30以降はそのまま計算18:30前に終了は0:00 最後にBの合計を出したいのですが、うまくいきません。 どうかよろしくお願い致します。 

  • エクセルを使った時間計算

    現在、給与計算のためにエクセルで残業を処理しようと思っています。 下記のように、勤務時間などから残業時間を計算し(TIME関数を利用)、時・分と別々のセルに表示させることは出来たのですが、月に何時間何分(それぞれ別のセルに表示)の残業をしたか、合計を出すことが出来ません。 どのような式を入れればよいか教えてください。 宜しくお願いします。    A  B   1  時  分     2  1  30 3  2  00 4  1  00 ・ ・ ・     合計 ?  ?

  • Excelでの時間計算について

    A列に時間、B列に分、C列に秒を入力し、その時間差を計算して、Dに表したいと思います。 例 A1に10(時)、B1に10(分)、C1に10(秒)を入力し、A210(時)、B2に20(分)C2に5(秒)を入力した場合、Dに9分55秒 このように表示させるにはどうしたらいいでしょうか。 よろしくお願いします。

  • エクセルの時間計算について

    エクセルで時間の計算をしたいのですが、うまくできず困っています。 A列には 1315、747、など3~4桁の数値が、B列にも 1532、916など同じく3~4桁の数値が入っています。これはそれぞれ 13:15、7:47、15:32、9:16のように時刻を表しており、A列は活動開始時刻、B列は活動終了時刻となっています。 C列には「TEXT(A2,"0!:00")-TEXT(B2,"0!:00")」という数式を入力し、実質活動時間を計算しています。 このようなデータが約9000行あります。C列の活動時間を合計して、時間数を出したいのですが、どのような計算式を用いればよいでしょうか?SUMでは出ないようですので・・・ ややこしいですが、お知恵をお貸し下さい。よろしくお願いします。

  • EXCELで関数を使って残業時間の計算がしたいのですが・・

    EXCELを使って残業時間の計算したいと思っているのですが、関数にあまり詳しくないのでアドバイスお願い致します。 (A1)出社、(B1)退社時間は30分単位で自分で入力します。 D1に10時間以降残業にし、残業に満たない場合は未表示で30分単位は0.5と表示させようと思っています。 今現在 A1=出勤時間 8:00 B1=退社時間 20:00 C1=労働時間 12:00   関数  =IF(A1="","",B1-A1) D1=残業時間 =IF(C1="","",IF(C1>"10:00",(C1-"10:00")*24,"")) と、いう感じにやってみましたが、D1の計算がうまくいきません。よくわからないのですが、シリアル値というのが関係してるのでしょうか?宜しくお願い致します。

  • 時間の計算

    残業時間の合計をExcelで求めたいと思っています。 例えばA1に1時間20分、B1に2時間10分と記入して C1にA1とB1を合計した3時間30分と表示させたいです。 普通に入力して足し算をしたのではC1の値が0になってしまいます。 うまく表示させる方法があったら教えてください。

  • エクセルで時間計算(勤怠)の仕方を教えて!!

    エクセルを使って、勤怠時間表を作りたいんです。 <決まりごと> 始業時間…後の時間で30分単位(例 8:47→9:00、9:12→9:30) 終業時間…前の時間で30分単位(例18:20→18:00、19:59→19:30)休憩時間…1時間    残業時間…18:00以降、30分単位 深夜残業…22:00以降、30分単位 ※9:15に始業18:15に退社だと、実働時間は7.5時間で残業は0です。 例えば A始業時間 B終業時間 C実働時間 D通常残業 E深夜残業   9:17   22:40   12.0H   4.0H    0.5H AとBを入力すれば、C~Eがでてくる、というエクセルの計算式を教えて下さい。  

  • Excelで 時間計算するには?

    ExcelのA列に、たとえば、時刻「AM8:50」と入力して、入力されたAに、任意の時間(2時間後、とか30分 後)の時間を計算して、B列に表記するには、どうすればいいですか? もちろん表記も「AM、あるいはPM」です。

専門家に質問してみよう