Excel（エクセル）活用コミュの時間の計算について教えて下さい。

前のトピはタイトルの説明不足と、本文の説明でバージョンを書いていませんでした。
再度上げさせていただきます。
管理人様、皆様申し訳ありません。

質問内容ですが
エクセルで残業時間を管理するシートを作ろうと思っています。
Excelのバージョンは2013です。
Excelについてはほぼ初心者で、ネットで色々調べたのですがなかなか解決出来ずこちらに来ました。
よろしくお願いします。

勤務時間は
9:00～17:45
でこの時間以前、以降は超過勤務扱いです。

例 22時に退勤していた場合
A1 17:45
A2 －22:00
B1 4:15 (超過時間)
C1 0:30 (休憩時間)
D1 3:45 (実際に働いた時間)
C1 累計

という形にしたいのですが、どうすればいいでしょうか？
BにAの引き算をいれても変な数字になります。(正確な数字ではない)
またAには 1745と入力すると17:45という表示にしたいです。
累計では60分で1時間にくり上がらず困っています。

分かりにくい文章ですが、よろしくお願いします。

コメント(15)

最初
全て
最新の40件

[1] mixiユーザー 12月04日 23:13

画像のような感じであれば、普通に引き算で出来ると思いますが

画像ではA1セル、A2セル、C1セルは手入力、
B1セル、D1セルは引き算で算出しています

>BにAの引き算をいれても変な数字になります。
変な数字とはどういったものでしょうか？

>またAには 1745と入力すると17:45という表示にしたいです。
「1745」という数値の表示を「17:45」というようにすることは可能ですが
時間として認識してくれませんよ？

「1745」と入力して、時間として認識させ「17:45」としたいのであれば
別のセルでよければ関数で出来ますが、入力したそのセルで
ということになると、マクロ(VBA)を組むしかありません

[2] mixiユーザー 12月04日 23:22

>>[1]
ありがとうございます！
画像の通りです！
1745を17:45にするために、書式の設定の方で表示させていた為に引き算の方で正確な数字が出なかったのかもしれません！

別のセルで大丈夫なので、17:45という時間を認識させる関数を教えていただけますか？

また累計の方で足していくのですが、3:15と前の日の超過時間4:50を足すように入力しても7:65という数字が出ます。
60分を超えると繰り上がり、7:05という数字にする方法(関数)はありますか？

たくさん質問をしてしまい申し訳ないです。

[4] mixiユーザー 12月04日 23:32

>>[2]
>別のセルで大丈夫なので、17:45という時間を認識させる関数を教えていただけますか？
いくつか方法はありますが、TIME関数とRIGHT関数、LEFT関数、VALUE関数を組み合わせるとかですかね

>また累計の方で足していくのですが、3:15と前の日の超過時間4:50を足すように入力しても7:65という数字が出ます。
これも数値で315+450という認識になっているのではないでしょうか？
Excelでは時間や日付はシリアル値という数値で管理していますので
時間や日付の計算ではシリアル値に基づいた時間データ、日付データを計算しなくてはいけません

[5] mixiユーザー 12月04日 23:36

>>[4]
そんなにたくさん方法があるんですか！？
TIME関数を使いたいのですが、どういう使い方ができますか？

315+450という認識になっているのかもしれません...

[6] mixiユーザー 12月04日 23:41

>>[5]
それぞれの関数の使い方はヘルプを読むなり、ネット検索するなりすれば
分かるはずですので、とりあえず流れだけ示します

LEFT関数で「17」を切り出し、切り出したものは文字列なのでVALUE関数で数値化
RIGHT関数で「45」を切り出し、切り出したものは文字列なのでVALUE関数で数値化
それぞれをTIME関数の時、分に入れる(秒は0で良いかと)

[7] mixiユーザー 12月04日 23:44

Googleで「Excel 4桁時間」などのようなキーワードで検索すれば
方法は出てくるはずですよ

[8] mixiユーザー 12月05日 03:11

>6で書いている流れは時刻を4桁で書いている場合は簡単ですが
「0805」「0030」というようにせず、3桁や2桁で「805」「30」などのように
なっている場合ですと若干面倒になりますので、別の方法のほうが
良いかと思いますね

[9] mixiユーザー 12月05日 08:58

B1=(INT(A2/100)/24+MOD(A2,100)/1440)-(INT(A1/100)/24+MOD(A1,100)/1440)
D1=B1-(INT(C1/100)/24+MOD(C1,100)/1440)

A1,A2,C1 はそれぞれ 1745,2200,30 と入力
B1,D1 の表示形式は”時間”に変更
累計は単純にD1を足していけばよい。

[10] mixiユーザー 12月05日 09:11

A1セルに時刻を示す数値を入力し、別セルで時間データとして算出するなら

時刻を絶対に4桁(1745、0805、0030、など)で入力しているなら
=TIME(VALUE(LEFT(A1,2)),VALUE(RIGHT(A1,2)),0)
=TIMEVALUE(LEFT(A1,2)&":"&RIGHT(A1,2))

3桁や2桁(1745、805、30、など)で入力する場合があるなら
=TIME(VALUE(LEFT(TEXT(A1,"0000"),2)),VALUE(RIGHT(TEXT(A1,"0000"),2)),0)
=TIMEVALUE(LEFT(TEXT(A1,"0000"),2)&":"&RIGHT(TEXT(A1,"0000"),2))
=TIMEVALUE(TEXT(A1,"00!:00"))
=TEXT(A1,"00!:00")*1

入力された数値を計算して、時分を算出し、TIME関数に与える方法もありますね
=TIME(INT(A1/100),MOD(A1,100),0)

ということで、B1セルでは時刻を4桁で入力しているなら例えば
=TIME(VALUE(LEFT(A2,2)),VALUE(RIGHT(A2,2)),0)-TIME(VALUE(LEFT(A1,2)),VALUE(RIGHT(A1,2)),0)

3桁や2桁も入力するのであれば例えば
=TEXT(A2,"00!:00")*1-TEXT(A1,"00!:00")*1

入力された数値を計算して、時分を算出し、TIME関数に与えるなら
=TIME(INT(A2/100),MOD(A2,100),0)-TIME(INT(A1/100),MOD(A1,100),0)

[11] mixiユーザー 12月06日 15:16

>>[6]
せっかく教えて頂けたのですが、私がうまく扱えず結局簡単な引き算でおさまりました。
ありがとうございました！

[12] mixiユーザー 12月06日 19:12

>>[11]
>簡単な引き算でおさまりました。
時間データ(シリアル値)での簡単な引き算であれば、全く問題ないと思います
これが数値(1745など)での引き算では時間計算をする上では厳しいと思いますよ

画像の1枚目のように入力する時間を全て4桁で入力するのであれば、例えば
< 方法例 >
A1セル 1745
A2セル 2200
B1セル =TIME(VALUE(LEFT(A1,2)),VALUE(RIGHT(A1,2)),0)
B2セル =TIME(VALUE(LEFT(A2,2)),VALUE(RIGHT(A2,2)),0)
C1セル =B2-B1
D1セル '0030　　　← 文字列で「0030」
E1セル =TIME(VALUE(LEFT(D1,2)),VALUE(RIGHT(D1,2)),0)
F1セル =C1-E1

画像の2枚目のように入力する時間が2桁や3桁の場合もあるとすれば、例えば
< 方法例1 >
A1セル 1745
A2セル 2200
B1セル =TEXT(A1,"00!:00")*1
B2セル =TEXT(A2,"00!:00")*1
C1セル =B2-B1
D1セル 30
E1セル =TEXT(D1,"00!:00")*1
F1セル =C1-E1

< 方法例2 >
A1セル 1745
A2セル 2200
B1セル =TIME(INT(A1/100),MOD(A1,100),0)
B2セル =TIME(INT(A2/100),MOD(A2,100),0)
C1セル =B2-B1
D1セル 30
E1セル =TIME(INT(D1/100),MOD(D1,100),0)
F1セル =C1-E1