エクセルで時間の表示をしたい

質問

勤務表を作ってます。
そこで初歩的なことだと思いますがお聞きしたいのでお願いします。

実働時間を「00：00」という書式にしたいのですが
セル一つ一つにセミコロンを入れなくても
数字を入れるだけでセミコロンが出てくる、
なんて言う便利な設定をご存知の方、教えてください。よろしくお願いします。

回答No.5

sero · ベストアンサー

普通に「12:45」とセミコロンを入れて時刻としてExcelに認識させた場合は
普通に足し算や引き算が可能です。
例えばA列に始業時間、B列に終業時間、C列に差を自動計算させるとして
　　　A　　　　B　　　　C
1　12:45　　16:00　　
2　07:30　　17:30

という表で計算するとC1に「=B1-A1」と入れれば「3:15」という解が帰ってきます。
間に休憩時間が1時間あるとすれば「=B1-A1-"1:00"」でOKです。

もしA列に始業時間(時)、B列に始業時間(分)、C列に終業時間(時)、
D列に終業時間(分)、E列に差を自動計算させるとして
　　A　　B　　C　　D　　E
1　12　　45　16　　0
2　07　　30　17　　30

という表で計算するとE1に「=TIME(C1,D1,0)-TIME(A1,B1,0)」と入れれば
「3:15」という解が帰ってきます。
間に休憩時間が1時間あるとすれば「=TIME(C1,D1,0)-TIME(A1,B1,0)-"1:00"」でOKです。

計算結果の表示形式が「**:**」にならなければ、セルの書式設定で
時刻カテゴリから「13:30」を選択して下さい。

お礼

本当にありがとうございます！
前者のやり方で出来ました。
でも、また疑問が生じてしまいました。(泣)
前者のやり方でやるとして、
「=B1-A1」が一日の実働時間ですよね。
では、その実働時間月の合計を出す場合って
オートサムで「C1：C2」とすればいいんですよね？
このように2日分だとちゃんと計算されるのですが
3日分以上になると「3：10」とか、極端に少ない数字で計算されてしまうんです。
時間の計算の場合って月の合計までは出せないんでしょうか？

何度もしつこくすいませんが、もしよろしければお願いします。

回答No.8

imogasi

既回答を十分読めてないですが、参考までに。
表示形式とセルの値についての理解に（ご質問者は）混線があるようです。
１３：３０などの：を入れるのは、見た目で：を出すだけでなく、大切なのは、エクセル（システム）へ時間値ですよとしらせているものです。１３：３０をセルに入れると実際は13/24+30/(24*60)の値をエクセルは入れます。（注）
１３３０と入力すると、エクセルへ時間ですよという知らせが出来ません。表示（見た目）だけを１３：３０にしたければ、##":"##で書式（ユーザー）設定をすると、そう見えます。値は１３３０のままです。
最終的に13/24+30/(24*60)の値にしておかないと、エクセルの時間機能（演算及び表示形式機能）が全く使えません。このように後で自分で演算すると言うことは、１３３０と入力した列とは別の列が必要ということです。それでも入力に１桁楽な方を採りますか。相当多数の入力なら元が取れるかもしれないが。
(注）２４時間を１とする単位で計った数。２４時間を越える場合は、普通は日に繰り上げる。

回答No.7

maruru01

こんにちは。maruru01です。

別の方法です。
セルに、「630」「1245」のように3・4桁で入力して、セルの表示形式を、No.2の方のように、
「0":"00」
するとします。
これを計算に利用する場合は、
「--TEXT(A1,"0!:00")」
のようにします。
そして、A1に開始時刻、B1に終了時刻の場合、

=TEXT(B1,"0!:00")-TEXT(A1,"0!:00")

で出来ます。
セルの表示形式は、合計欄も含めて、No.6の方のように、
「[h]:mm」
とすることで、延べ時間で表示されます。

次に、「:」（セミコロンじゃなくて、コロンです）を省略したい理由が、テンキー部分だけで入力したいということなら、
オートコレクトを利用する方法があります。
メニューの[ツール]→[オートコレクト]で、
まず、[入力中にオートコレクト]にチェック（確認）して、
そのリストに、
[修正文字列]：「..」（ピリオド2つ）
[修正後の文字列]：「:」（コロン）
を追加します。
これで、ピリオドはテンキー部分にあるので、
「12..45」
と入力すれば、
「12:45」
と変換されます。
この場合は、時刻データが入るので、計算は普通に

=B1-A1

とかで出来ます。

ちなみに、「..」にしたのは、ピリオド1つだと、普通に小数点として使用出来なくなるためです。

お礼

とてもわかり易い方法ですね！
大変参考になりました。TEXT関数は見るのも使うのも初めてなので大変いいお勉強になりました。
おかげ様で自分なりに納得の行くものが作れました。
ありがとうございました。

回答No.6

mshr1962

>3日分以上になると「3：10」とか、極端に少ない数字で計算されてしまうんです。

そのセルの書式設定の表示書式をユーザー定義で
[h]:mm
としてください。
24:00以上の表示が可能になります。
現在は27:10を1日と3:10として時刻部分だけ表示しています。

お礼

[h]:mm　という表示形式、大変参考になりました。
活用できましたよ！
つくづく奥が深いです。皆さんのおかげで思うようなものが仕上がりました。
ありがとうございました。

回答No.4

s_yoshi_6

＃2の方がおっしゃるように、セルの書式設定で「00":"00」とすれば、見た目上は例えば「12：50」という形で表示されますが、実際は「1245」という数値であって、時刻ではありません。
ですから例えば、Ａ１が「12：50」、Ｂ１が「15：30」の時、通常通り時刻で入力してあれば実働時間は「=B1-A1」と入力すれば「2:40」と出てきます。
しかし、「1250」「1530」という数値（見た目上「：」付き）で入力された場合、「280」という数値となり、前述の書式設定を使っても「2:80」としかなりません。

もし出社、退社時刻はＡ１、Ｂ１に４桁の数値で入力し、それを使ってもし実働時間を出そうと思ったら、例えば、
Ａ１のセルの値を時刻に換算した「INT(A1/100)/24+(A1-INT(A1/100)*100)/24/60)」
Ｂ１のセルの値を時刻に換算した「INT(B1/100)/24+(B1-INT(B1/100)*100)/24/60)」
を使って適当なセルに
=(INT(A1/100)/24+(A1-INT(A1/100)*100)/24/60)-(INT(B1/100)/24+(B1-INT(B1/100)*100)/24/60)
とすれば（セル書式は「時刻」）、正しい実働時間が表示されます。

お礼

方法としてはいろいろあったんですね。
皆さんの回答を組み合わせて
思い通りのモノが出来あがりました。
ありがとうございました。

回答No.3

sero

#2の方法はあくまで表示上のもので、時間・分の計算には使えません。
時間と分を別セルに入力すれば関数によって別セルに表示・計算がかのうですね。
（12:45ならA1セルに12、B1セルに45等）

お礼

ありがとうございます。
それならできそうです。
計算のことに関して書き込んでいただけたので
甘えてもうひとつお聞きしたいのですが

例えば
＞時間と分を別セルに入力すれば関数によって別セルに表示・計算がかのうですね。
（12:45ならA1セルに12、B1セルに45等）

と表示すれば時間の集計が出せるんですよね？
じゃあ、もし
普通に手入力で「１２：３４」と入れた場合、計算は出せますでしょうか？
もし知っていたら度々で申し訳ないのですが
併せて教えてください。よろしくお願いします。

回答No.2

sero

入力したいセルを選択し、セルの書式設定で、ユーザー定義を選び、
種類(T)の所に
00":"00
と入力すれば、1234と入力すると12:34になります。

回答No.1

atonement

(1)セルを右クリック
(2)『セルの書式設定』
(3)表示形式タブの分類(C)の項目から『ユーザー定義』を選択
(4)右の種類(T)の下の欄に表示される中から『h:mm』というものを選択する。

一応これで『○：○○』という感じで表示されると思います。ただ入力される数値には注意が必要かと思います。

『h』は時、『m』は分、『s』は秒を表します。
もし『09:05:02』という感じに表示させたいなら『hh:mm:ss』ということになります。
項目が種類(T)の一覧の中になければ、種類(T)のすぐ下の欄に『hh:mm:ss』と入力すれば出来ます。

こんな説明で分かるかなぁ？

お礼

ありがとうございます。
しかし、実は既にこれは試みていたのですが
数字を入れるとすべて「00：00」と表示されてしまったので断念したんです。
多分何かやり方が悪いのでしょうが・・・。