勤務時間の時間の丸め方教えて！

質問

エクセル2003で今までオートコレクトを使用して
セルに7.31と入力7：31と表示させていました、
そして、7.31と入力すると別のセルに数式（CEILINGセル,1/24/60*15）として7：45と丸めて表示させ、
17.13と入力すると別のセルに数式（FLOORセル,1/24/60*15）として17：00　と丸めていたのですが、
セルの書式設定→ユーザー定義で　＃＃":"＃＃とすれば、
735と入力すれば7：35となるのを知りセルに設定したところ入力したセルでは、7:35と表示されるのですが別のセル(数式を入力して丸めていたセル）に7:35と表示されてしまいます。
このセルで時間を丸めて7:45にするにはどうすればいいのでしょうか？
あと、1713と入力したときに17:00と表示させる方法もお願いします。
教えてください、宜しくお願いします！

回答No.8

A88No8 · ベストアンサー

こんにちは
ANo.7です。　レス待っていましたよ(^^)

>画像で開始時と終了時は、時間では表示できないのでしょうか？

　そんなことはありません。ANo.7で整理した例には、必要ないかと思って（例だから）省略しました。ごめんなさい。
　ただ、計算はすべて分換算した状態で行ない、時刻表示をするときは整数変換をかませた上で表示形式を ##":"## にしましょう。　計算をすべて分換算したデータで行なうことによって分からの時への繰り上げ処理を省くことが出来るのです。

★これだけは忘れないでください。
(a) 計算を複雑にしないために、時刻表示の整数データで計算しない！！
(b) 必ず分変換したデータで計算する。
　　分からの時への繰り上げ処理を省くことが出来る。

で、例をＣとＤ列の分データを整数変換してＩとＪ列に作るとします。
することは、整数変換の式（Ｈ列の式）でＣとＤ列のセルをＩとＪ列から参照させ、お約束の表示形式に変更するのです。

Ｉ列
　　項目：開始時刻表示
　　内容：整数変換の式
　　　式：=INT(C7/60)*100+MOD(C7,60)
Ｊ列
　　項目：終了時刻表示
　　内容：整数変換の式
　　　式：=INT(D7/60)*100+MOD(D7,60)

>それと、実労時間の合計を出したかったのですが、例えば79：30なのに78：90のように繰上げになりませんでした？合計はSUMで出しました。
　これは、Ｈ列の整数データで合計を計算させたからと思います(^^;
　このように実労時間の合計を出すときは、（Ｈ列ではなくて）Ｇ列の分変換済みデータを計算してＨ列の式でＨ列に時間表示させればよいのです。　Ｇ列で計算することによって、分からの時への繰り上げ処理を省くことが出来るのです。

下の例では、Ｇ列の総合計をG63で計算します。
その時刻表示は、Ｈ列のH62のセルをH63にコピーして式を作ります。

Ｇ列
　　項目：実労時間の総合計
　　内容：Ｇ列の実労時間計算結果の範囲すべてを足し込む
　　　式：=SUM(G2:G62)
Ｈ列
　　項目：実労時間
　　内容：Ｇ列の実労時間の総合計を整数変換の式で変換して時刻表示する
　　　式：=INT(G63/60)*100+MOD(G63,60)

　いうまでもないと思いますが、実務に使用するときは「時刻表示」をさせる（＝ユーザに見せる部分）と分変換した列（作業用でユーザに見せる必要はない）を分けた方がよいと思います。

お礼

分かりました。有難うございます。
これでようやく、タイムカードの集計等が簡単になります。
これから表を、作りたいと思います。
また、分からない事があったら宜しくお願いします。

丁寧に教えていただき有難うございました。

回答No.7

A88No8

こんにちは

　時間と分を分けたデータにすると計算が複雑になりました。
　そのため、質問者さまが混乱し始めていると感じました。

　そこで、これからは基本的に．．

(1) 整数４桁のデータを入力
(2) データを分に変換＋切り上げ／切り捨て処理
(3) 計算処理
(4) 整数４桁変換処理

　という流れで考えていきましょう！

　以下に上記の４つの処理を各列（Ａ～Ｂ、Ｃ～Ｆ、Ｇ、Ｈ）に割り振った
例を書きました。参考にしてください。

Ａ列
　　項目：出勤時刻
　　内容：整数データ
Ｂ列
　　項目：退勤時刻
　　内容：整数データ

Ｃ列
　　項目：開始時(分)
　　内容：分変換＋切り上げの式
　　　式：=INT(A7/100)*60+CEILING(MOD(A7,100),15)
Ｄ列
　　項目：終了時(分)
　　内容：分変換＋切り捨ての式
　　　式：=INT(B7/100)*60+FLOOR(MOD(B7,100),15)
Ｅ列
　　項目：休憩時間
　　内容：整数データ
Ｆ列
　　項目：休憩(分)
　　内容：分変換の式
　　　式：=INT(E7/100)*60+MOD(E7,100)

Ｇ列
　　項目：実労時(分)
　　内容：時間計算式
　　　式：=D7-C7-F7

Ｈ列
　　項目：実労時間
　　内容：整数変換の式
　　　式：=INT(G7/60)*100+MOD(G7,60)

最後に縮小されて見ので部分的なハードコピーを添付します。これで時刻境界の正しさを検証してください。

お礼

何度もありがとうございました。
また、質問させてもらってよろしいでしょうか？
画像で開始時と終了時は、時間では表示できないのでしょうか？
それと、実労時間の合計を出したかったのですが、
例えば79：30なのに78：90のように繰上げになりませんでした？
合計はSUMで出しました。

何度も申し訳ありませんが宜しくお願いします！

回答No.6

A88No8

こんにちは
No.1です。
もう一言付け加えなければと思っていたら．．
そう、分を切り上げて６０になったとき１時間にする必要が発生します。
元々のデータが時刻ではなく最大４桁の整数なので時間の桁には百の位が入るわけで時刻の「時」ではありませんよね。
１：００表示の１００は、６０分ではなく百ですから。No.5さんのしてくださった解説と同じです。

で、直感で恐縮ですが、IF文で「分」45分以上の時の場合分けの工夫が必要です。４５分以上なら「時」に繰り上げるといった．．
切り捨てる方はいっさい考慮しないで検証もしていませんが
=if(mod(735,100)<45,735-mod(735,100)+CEILING(mod(735,100),15),735-mod(735,100)+100)
こんな雰囲気になるかと．．(^^; ※例によって735は適当なセル名

あと時分すべてを分に直してから計算式を考える方法も有りです。
=mod(int(735/100)*60+CEILING(mod(735,100),15),60)*100+mod(CEILING(mod(735,100),15),60)
検証してないけど、IF文で場合分けしなくても良さそう．．

切り捨ての方で７：００（＝７００）のときは、うまくいっていますか？

日付型は、もともと日時を表現するためのデータ型らしく小さい時刻をたくさん足して時間を求めようとすると計算が合わなくなったりね。　σ(^_^;は、時間計算とお金の計算で整数化するのは悪くないと思いますが、つじつま合わせが面倒ですね。　まじめな時間計算機能をエクセルにやって貰いたい（笑）

お礼

ありがとうございました。
時間の丸めと繰上げうまくいきました。
でも、又問題発生でたすけてもらえますか？
終了時刻－開始時刻－休憩時間＝実労時間を求めたいのですがうまくいきません。
終了時刻セルには=D7-MOD(D7,100)+FLOOR(MOD(D7,100),15)
開始時刻セルには=IF(MOD(C7,100)<45,C7-MOD(C7,100)+CEILING(MOD(C7,100),15),C7-MOD(C7,100)+100)
休憩時間セルには=200となっています。
セルは整数入力＝時間表示になっています。
何度も申し訳ありません。
宜しくお願いします。

回答No.5

gyouda1114

＞756で入力すると7:60になってしまいます？
756は時間ではありませんから、
=A1-MOD(A1,100)+FLOOR(MOD(A1,100),15)の式では
=A1-MOD(A1,100)で700を抜き出し
=CEILING(MOD(56,100),15)で残りの56を15単位で切り上げて60にしますから
7:60に当然なります要するに760を7:60と表示させているだけだからです。
7:56を時間として15分単位で切り上げ8:00にするのならA1セルには7:56と入力しなければなりません。
要するに756と入力するのでは時間計算はできません。

回答No.4

noname#204879

「735と入力すれば」「別のセル(数式を入力して丸めていたセル）に」「時間を丸めて7:45にするには」=CEILING(TEXT(A1,"0!:00"),"0:15")

「713と入力すれば」「別のセル(数式を入力して丸めていたセル）に」「時間を丸めて7:00にするには」=FLOOR(TEXT(A2,"0!:00"),"0:15")

回答No.3

gyouda1114

＞1713と入力したときに17:00と表示させる方法もお願いします。
難しいと思います
1713と入力して17:13と表示させるだけなら書式設定 → 表示形式のユーザー定義で##":"##又は00":"00でできますがこれは見かけ上と1713を17:13表示させているだけです。
したがって、回答番号：No.1さんの回答の方法を使えば
A1に1713と入力し、B1セルに　=A1-MOD(A1,100)+FLOOR(MOD(A1,100),15)　で17:00と表示されます。

私が時間として17:13と簡単に入力する方法は、
「:」の読みとして「..」を単語登録しています。「17..13」と半角でセルに入力すると17:13と表示されます。
お試しを。

A1に17:13と入力し、B１に　=FLOOR(A1,"0:15")　で17:00と表示されます。

お礼

回答ありがとうございました。
1713で17:00になりました。
でも
756で入力すると7:60になってしまいます？
お分かりになれば。教えてください！お願いします。

回答No.2

marbin

参考まで。

給料・時給の計算は何分単位？
http://www.roudousha.net/zangyo/002time_cut.html

回答No.1

A88No8

こんにちは
7:35の方をなぜか？考えてみると．．
入力した値は、整数の７３５です。CEILINGは、指定した最小倍数に切り上げる関数だからね～。
７００と３５に分けて
=mod(735,100)=35
=735-mod(735,100)=700
※735はセルに入っている
３５を１５で切り上げ
=CEILING(35,15)=45
結果を７００に加える
=735-mod(735,100)+CEILING(mod(735,100),15)=745
こんな考え方。
735は適当なセルに置換えてね。

補足

回答ありがとうございました。
でも、(出社セル)に756で入力すると7:60になってしまいます？
それと(退社セル)に1713と入力しても17:00にはなりませんでした。
お分かりになれば。教えてください！お願いします。