[[20150308111712]]　『時間の合計』（kano）　ページの最後に飛ぶ

『時間の合計』（kano）

はじめまして。
時間の計算ができず困っているので教えてください。

以下の２列は、どちらも別セルからの計算で出ている時間です。
（A列：退勤時間-出勤時間、B列：退勤時間-定時時間）

A列 B列
12:18　 1:13
11:59 0:49
12:04 0:59

オートカルクで合計を見ると、
A列：36:21:00
B列：3026739:01:00
と表示されます。
何が違うのでしょうか？

おそらく同じ理由で、B列と同じような状態になっているセルに
8時間以下は表示しないという計算式　IF(X16>8/24,X16-8/24,"")　を入れても、
7:49を8時間以上と認識し、23:49という結果が出てしまいます。

どのようにすればA列のように計算ができるようになるのかわかりません。
どなたか教えていただけると大変助かります。

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 別セルにどんな値が入っていて、B列はどんな数式なのか、セルの表示形式はどうなっているのか、
 こちらでも再現できるような情報を提示してください。

 > 8時間以下は表示しないという計算式　IF(X16>8/24,X16-8/24,"")　を入れても、 
 > 7:49を8時間以上と認識し、23:49という結果が出てしまいます。

 とりあえず、これについてですが
「7:49」となっているセルの表示形式をユーザー定義で
 [h]:mm
 にしたら「31:49」になりませんか？
　
（JKT） 2015/03/08(日) 12:36

 B列は日付も入ってる？
 A・B列の表示形式を標準にした結果を
 書いてもらえますか？
（コナミ） 2015/03/08(日) 12:46

数式がいろいろ絡み合っていて説明がしづらいのですが、
まずこのシートは下記サイトからダウンロードしたものです。
http://time-recopon.jp/service/excel.html
ここにアレンジを加えています。

（わかりやすいようにＡ列、Ｂ列としましたが、実際はＭ列とＯ列です。
ここではＡ列とＢ列のままでいきますね。）

Ａ列に入っている数式は
=IF(J12<>"",IF((J12-P12)>0,J12-P12,"ERROR"),"")
です。

Ｊ列は、=IF(J12<>"",IF((J12-P12)>0,J12-P12,"ERROR"),"")　です。
Ｐ列は、=IF(I12="",P11,I12)
I列は、=IF(OR(D12="",F12=""),"",IF(D12=1,CEILING((E12+F12),TIME(0,$M$2,0)),""))
Ｄ列は、1~4のどれかの数字が入っています。（1:出勤、2:退勤、3:休憩入り、4:休憩戻）
Ｅ列は、打刻の日付
Ｆ列は、打刻の時間
Ｍ２セルは、丸めの設定として数字の1が入っています。

こんな感じで状況がわかるでしょうか？

7:49を [h]:mmで見ると、1008919：49と表示されます。

Ａ列の　12:18 を標準に見ると、0.5125 と表示されます。
Ｂ列の　1：13　を標準で見ると、42037.05069　と表示されます。

（kano） 2015/03/08(日) 14:00

 > Ａ列に入っている数式は 
 > =IF(J12<>"",IF((J12-P12)>0,J12-P12,"ERROR"),"") 
 > Ｊ列は、
 > =IF(J12<>"",IF((J12-P12)>0,J12-P12,"ERROR"),"")

 この2つの数式、全く同じですけど間違いありませんか？

 A列とB列のままでいくということですが、B列の数式はどれでしょうか？

 もしかして ↓ ですか？
 > I列は、
 > =IF(OR(D12="",F12=""),"",IF(D12=1,CEILING((E12+F12),TIME(0,$M$2,0)),""))

 だとすると、
 > B列：退勤時間-定時時間

 この説明と合わないんですが、一応そのI列がB列のことだとして、
 ・E列の日付とF列の時刻を足している理由
 ・CEILING関数を使っている理由（M2が「1」ということは、F列は秒単位まで入力してる？）
 は何ですか？

 具体的な値で言うと
 E列:　2015/2/2
 F列:　1:13
 この場合、B列はどうなればいいんですか？
　
（JKT） 2015/03/08(日) 15:24

ごめんなさい、Ｊ列を間違えていました。
Ｊ列は、=IF(D12<>2,"",FLOOR((E12+F12),TIME(0,$M$3,0)))　でした。

Ｂ列は、=IF(OR($D12=1,$D12=3,$D12=4,$D12=""),"",IF(OR($V12=5,$V12=7),$J12-$X$3,$J12-$X$2))　です。
Ｖ列には、日にちから出された曜日が入っています。
（月、火、水、金が19:30あがり、木、土が12:00あがりです。）

Ｉ列は、出勤時間だけを表示させている列で、結果として打刻そのままの数字が表示されています。

Ｆ列　　Ｇ列　　I列Ｊ列Ｋ列　Ｌ列　　　Ｍ列（Ａ列のこと）Ｎ列　　Ｏ列（B列のこと）
8:25　　出勤　　8:25
12:01　休憩入　　　　　　　　　12:01
13:03　休憩戻　　　　　　　　　　　　　13：03　　　　　　　　　　　　1：00　　
20:43　退勤　　　　　　20:43 　　　　　　　　　　12:18　　　　　　　　　　　　1:13

たとえばこれが一日分です。
Ｂ列（ここではＯ列）で、退勤時間から定時（19:30）を引いて残業を出しています。
この残業の週の合計を出したいのですが、足し算ができないのです。

これで伝わりますでしょうか？

（kano） 2015/03/08(日) 15:54

 > Ｊ列は、=IF(D12<>2,"",FLOOR((E12+F12),TIME(0,$M$3,0)))　でした。

 質問に答えてもらってないんですが、
 このJ列で、日付と時刻を足し算している理由は何ですか？
 FLOOR関数を使っているのは、時刻を秒単位まで入力しているからですか？

 とりあえずB列（O列？）がとんでもない値になる原因だけ言っておくと
 時刻に日付を足してるからです。
　
（JKT） 2015/03/08(日) 16:14

本当ですね！
日付と時間が足されていることに気がつきませんでした。
ダウンロードしたまま使用しており、今まで問題がなかったので見てもいませんでした。
floor関数もダウンロードしたまま使用しているのですが、M3の丸め設定を5にすると5分単位に
丸められるので分単位だという認識です。（8:43→8:45になる）

今からJ列を修正してみます。
あまり得意ではないので時間がかかるかもしれませんが、結果をまた報告させていただきます。

（kano） 2015/03/08(日) 16:30

すごく混乱してきました。

Ｊ列をシンプルに時間だけ表示させるようにすると、今度は勤務時間計（Ｍ列）がERRORになります。

Ｍ列は、=IF(J12<>"",IF((J12-P12)>0,J12-P12,"ERROR"),"")
Ｐ列は、=IF(I12="",P11,I12)

Ｐ列はその日の出勤の日付と時間を表示。（20152/2 8:25）

Ｊ列はどのように修正すれば良いでしょうか。。。？
（kano） 2015/03/08(日) 16:44

 > Ｊ列をシンプルに時間だけ表示させるようにすると、
 > 今度は勤務時間計（Ｍ列）がERRORになります。

 P列が「日付＋時間」だからですよね。

 P列に日付を入力せず、時間だけにするか、
 または日付は入れたままで、数式で時間だけ取り出すか、ですね。

 日付を足す理由があるんでしたら、J列は「日付＋時間」のままにして
 X2とX3に日付を足さないとダメです。

 日付は無視していいんですか？
　
（JKT） 2015/03/08(日) 17:10

日付は無視してもいいです。
おそらく、あとで確認しやすいように日付と時間をセットで表示しているのだと思います。

X2とX3に日付を足すとなると、その月すべての日にちの入力が必要になりますよね？

日付無視にするにはどうすればいいでしょうか？

（kano） 2015/03/08(日) 17:55

 確認です。

 出勤と退勤が日付をまたぐこと（退勤が深夜零時以降になること）はないんですか？

 > Ｐ列はその日の出勤の日付と時間を表示。

 手入力なのか、数式なのかよくわかりませんが、
 P列を時間だけにすることはできないんですか？
　
（JKT） 2015/03/08(日) 18:56

日付をまたぐことはありません。

A列～F列までは打刻されたものを値だけペーストして貼り付けています。
P列は数式　IF(I12="",P11,I12)　で表示されるようになっています。

P列に手入力で8:25と入力すると、表示が1900/1/0 8:25 となり、Mの勤務時間が1008900:18 となります。
P列を時間だけにするにはどうすればいいですか？

（kano） 2015/03/08(日) 19:08

 > P列は数式　IF(I12="",P11,I12)　で表示されるようになっています。

 それは前のコメントに書いてあるのを見ました。
 P11の値が手入力なのか、数式なのかわからなかったんです。

 > P列に手入力で8:25と入力すると、表示が1900/1/0 8:25 となり

 それは正しい表示です。何も間違っていません。
 表示形式を「時刻」にしてください。

 > Mの勤務時間が1008900:18 となります。

 そうなるのはJ列で日付を足しているからでしょ？
　
　
 P列は手入力で時刻だけを入力します。
 ↓ でどうなるか試してみてください。

 I列: =IF(OR(D12<>1,F12="",$M$2=""),"",CEILING(F12,TIME(0,$M$2,0)))

 J列: =IF(OR(D12<>2,F12="",$M$3=""),"",FLOOR(F12,TIME(0,$M$3,0)))

 M列: =IF(COUNT(J12,P12)<2,"",IF(J12-P12>0,J12-P12,"ERROR"))

 O列: =IF(COUNT(J12,V12,$X$2:$X$3)<4,"",MAX(0,J12-IF(OR(V12=5,V12=7),$X$3,$X$2)))

 表示形式「時刻」

 時間を合計するセルの表示形式は、ユーザー定義で　[h]:mm　にすること！
（24時間以上になる場合です）
　
（JKT） 2015/03/08(日) 21:30

時間の合計ができるようになりました！！！

改めてひとつひとつの関数を見直して理解できるようがんばります。

長い時間お付き合いいただきありがとうございました。
大変助かりました。

（kano） 2015/03/08(日) 21:50

コメント返信：

[ 一覧(最新更新順) ]

[[20150308111712]] 『時間の合計』（kano） ページの最後に飛ぶ

[[20150308111712]]　『時間の合計』（kano）　ページの最後に飛ぶ