【Googleスプレッドシート】残業時間や早退時間の合計を出したい

Question

おせわになります。現在会社の出退勤表を作成しており質問させていただきます。
Ｅｘｃｅｌ等大学で振れた程度で初心者です、申し訳ないです。

やりたいこと
9時5時を定時、5時6時の間の1時間をみなし残業とし、6時以降の残業がある場合残業代はでないですがその分違う日にはやく帰ったり遅く来たりしてよいことになりました。（フレックス制のようなかんじなんでしょうか…）
（例）今日は9時20時まで働いたので、別の日2時間早く帰ってよい。
なので出退勤時間を記録することで
【6時以降の残業の合計時間】ー【9時5時の間で休んだ時間（合法的な遅刻早退時間（？））】＝【現在代替的に休める時間の合計】
を出せるようにしたいです。

つまづいていること
1日ごとの勤務時間、超過時間（6時以降の残業時間）、短縮された時間（9時5時の間で休んだ時間）は出勤時刻、退勤時刻を打つことで自動ででるようにできましたが、それぞれの合計をSUMで範囲選択して出そうとすると変な数字になってしまい出来ません（画像下部参照）
それぞれの関数は下記の通りにしております。

例）
4行目の場合
勤務時間=C4-B4-K4
超過時間==IF(D4="", "",IF(D4-L4<=0,0,D4-L4))
短縮された時間=IF(D4="", "", IF(M5-D4<0,0,M5-D4))

勤務時間の合計=SUM(D4:D24)　＊数値が間違ってでる
超過時間の合計=SUM(E4:E24)　＊数値が間違って出る
短縮された時間の合計=SUM(F4:F24)　＊数値が間違って出る

説明が下手で申し訳ないのですが、わかる方がいればお願いいたします。

Answer 1

こんにちわ！
数値と時間を足していて、その結果が数値として表れているのだと思いました。
1時間は一日を24等分したうちの一つなので、1/24として、0.041666として表れてしまいます。

簡単な答え方としては、結果の表示方法を時間にしてあげればそれで事足りるのかと思いますが、
この計算方法だと、25時間とかの累計になった時に、(見えなくなる24時間)と残りの01:00という表記になり、
表記としては

01:00

が示されてしまうので、
それはやりたい事とは違うのではないだろうかと思って、
勝手にこうしたらどうかなという案を提案してみたいと思います。

=SUM(D2:D28)
の所を、以下の式にしてみるのはどうでしょうか？
=INT(VALUE(SUM(E2:E8) * 24)) & ":" & ROUND(MOD(VALUE(SUM(E2:E8))*24,1),2) * 60

まず、SUM(D2:D28)をVALUEにぶち込んで、強制的に数字に変換します。
SUM(D2:D28)が25:00の場合は、
VALUE(SUM(D2:D28))は、1.041666667とかになるはずです。
それに24を掛けた数字の、整数の部分は時間となり、1より少ない小数点以下の部分は分数になると考えて、
整数の部分と:と小数点以下の部分に60を掛けた数字を文字列として表示してみる
みたいなノリです。
細かい点であなた様のやりたい事と私が捉えた事が違うかもしれないと思っていますが、
とりあえず表示方法だけはやってみよー、というノリです。

Comments

[退会済みユーザー]

=INT(VALUE(SUM(D2:D28))) * 24 & ":" & ROUND(MOD(VALUE(SUM(D2:D28))*24,1),2) * 60
前半の「 INT(VALUE(SUM(D2:D28))) * 24」　について
仮にD2:D28の合計が1日未満となる場合、0となってしまうので、
=INT(VALUE(SUM(D2:D28))*24) & ":" & ROUND(MOD(VALUE(SUM(D2:D28))*24,1),2) * 60
とした方がよいと思うのですが、いかがでしょうか？

[退会済みユーザー]

修正反映前のものを見て書き込み始めていました。修正されていましたね。すみません。

[chimpoMAN]

おぉ！多分テレパシーかもしれません。

[haruka]

ありがとうございます！これからPCがさわれないので、明日朝やってみますm(__)m

Answer 2

１．表示を直したいセル（たとえば画像でいえば E25セル）をクリックします。

２．メニューの表示形式　から「数字」→「カスタム数値形式」を選択します。

３．ダイアログボックスのテキストボックスに半角で
[h]:mm
と入力し、適用ボタンをクリックします。