エクセルで入力された数値の時刻・時間計算する方法と表示形式変更で時刻情報に見せる工夫

Excelで時刻・時間の基本的な入力方法

一般的なキーボードでは文字入力部分と矢印キー等の部分、そして右側に数字入力のためにテンキー部分があります。

Excelで時刻・時間データを入力するには、半角数字（文字キー上部か右側テンキー）と「：（コロン）」を使用して入力します。

例えば12時34分や12時間34分といった値を入力したい場合、上記のような一般的なキーボードでは数値12と コロン：数値34と入力するのが基本な方法だと思います。

セルに入力を始め入力の途中は、上記のようにセル内の左側から「１２：３４」と表示されます。

１２：３４と入力に続いて、Enterを押下して入力を確定します。入力されたデータをExcelは数値と認識してセル内で右側に表示されます。

入力時には秒が省略されているので、時刻としての「秒」は０秒となっています。そのため数式バーには「１２：３４：００」と表示されています。

関連記事

「数値」と「文字列」の違いとは？セルへの入力でエクセルの基本特性を考えてみましょう。 エクセルの基本特性を知る エクセルにおけるサポート対応対応で、新元号令和の表示について問い合わせがあったこともあり、日……

テンキーだけで時刻・時間を入力したい

時刻・時間の入力では「：（コロン）」が必要ですが、コロンを入力するにはキーボードの文字部分のキーを押下しなければなりません。

時刻・時間入力のデータ数が少ない場合はさほど気にならないと思いますが、取り扱うデータによっては入力のデータ数が多い場合、数値入力に加えて「：（コロン）」入力のために文字部分を使う回数が増えることになります。

本記事で紹介する内容は、作業開始と終了の時刻から経過時間を求める処理で、開始時刻と終了時刻の入力を数が多いため入力作業の効率化も考えたい、という問い合わせから始まったサポートです。

右側にテンキーがある一般的なキーボードでの入力を想定で数字キーをのみを使うことで入力を行い、経過時間を求めることができるように考えていきます。

数値入力を時刻として表示する

数字キー（テンキー）を使って数値：１２３４を入力すると、もちろんエクセルは数値として認識するので、下の例のようにセル内で表示されます。

データ入力時に開始時刻・終了時刻として「：（コロン）」を使わず、４桁の数値「1234」を入力することで、時刻の表記となる「12：34」と表示できるようにセルの書式設定を変更します。

セルの書式設定を変更

セルに「１２３４」という時刻情報ではない数値を入力します。

セルの書式設定のユーザー定義で「#0”:”00」と設定することで、セルでの表示を時刻の表示に変更することができます。0を入力した場合０：００と表示されます。

作業時間は正しく算出できるか？

セルの書式設定を変更することで、入力した数値を時刻にみせかけることはできました。しかし、セルに入力された値はあくまでも1234という数値であって、時刻・時間データではありません。

作業にかかった時間を求める場合に、終了時時刻ー開始時刻で計算を行ってみます

作業１,２,６,７,８では計算結果としては正しく求められた形になっていますが、作業３では「＝１３０４ー１２３４」を計算して結果が７０となってしまいました。その他、作業９も正しく作業時間求めることはできません。

入力した値は1234という十進数の数値であるため、単純に引き算を行っても正しい結果を得ることができません。そこで経過時間を求めるには時刻・時間編へのデータ変換を行ってから計算を行うなどの処理が必要です

入力値と見た目の周知が必要

入力した値は「１２３４」という数値ですが、表示形式を変更したセルで表示されるのは「１２：３４」であるため、エクセルの時刻だと思ってしまい、エクセルの時刻・時間関数でその計算しても正しい計算結果を得られません。

また本記事で紹介しているセルの書式設定変更による処理は、このエクセルシートを使う個人・事業所のみで通用するいわゆるローカルルールとなるため、他のデータとのやり取りに注意が必要となります。

自分一人だけでこのような入力方法、書式設定の変更時刻・時間関数との組み合わせを行っている場合は問題ないかもしれません。

しかし事業所など大人数でこのようなデータの入力や処理を行うのであれば数値での入力についての説明、エクセルの数値と時刻・時間の関係など周知徹底が必要だと思います。

入力の効率化のための設定変更であることと、エクセルの本来の時刻・時間入力とは違うことを説明した上で数値を入力し時刻として表示できるようにエクセルで処理を行ってもよいとWebDreamは思います。

入力値を時刻・時間データへ変換

入力された１２３４の数値から頭2桁と下2桁の値を「時」と「分」として取り出し、TIME（）関数を用いて時刻・時間の計算ができる値に変換します。数値で入力した終了時刻部分にも同様の処理を行い最終的に処理時間を計算します。

入力値から「時」を求める

「１２３４」の値から「１２」を求める方法として、入力値を１００で割り、その値の整数値のみを計算することで時の値「１２」を求めます。

「１２３４／１００ → 12.34」が求められ、その値の整数値のみをINT（）関数で求めます。

入力値から「分」を求める

「１２３４」の値から「３４」を求める方法として、入力値を１００で割ったあまりをMOD（）関数で求めます。

算出した「時：１２」、「分：３４」の値をTIME（）関数で時刻・時間値として求めます。

時・分の値から時刻情報を求める

関連記事

エクセルでTIME関数を使い時・分・秒の3つの値から時刻情報（シリアル値）を取得する 時刻関数の活用 4月目前！進学、就職と新しい世界へと進む人であふれています。そこで勉強・仕事で使うであろうOffi……

変換した値で作業時間を算出

数値で入力した開始時刻と終了時刻を上記の時・分・TIME（）関数で変換し、最終的に変換終了時刻から変換開始時刻を引くことで、作業時間を求めます。

数値の単純引き算では正しく計算できなかった作業時間が正しく求めることができました。

まとめ

今回は数値で入力した値を時刻として表示できるよう書式設定を行い、時・分情報を算出しTIME（）関数を利用して時刻情報へと変換し計算を行ったことを記載しましたが、この方法でなくても作業時間を求めることができると思います。

開始時刻と終了時刻部分への入力をスピーディーに効率よく行うために、数値入力できるように考えてその入力された値から作業時間を算出する方法の一つと考えています。

時刻・時間情報を「：（コロン）」を使わず入力し、それを時刻情報として見せている点について「そんなのはエクセルの入力ではない」「時刻情報は：（コロン）を使って入力すべきだ！」という方もいらっしゃるかと思います。

もちろん一般的なキーボードであれば「：（コロン）」キーを押せば入力できますが、データ処理に特化し数値キーのみの、いわゆる「テンキー」単体での入力もあったため、テンキー部分のみで処理することを考えて、数値入力での処理を行っています。

今回の例では時・分からTIME（）関数で変換し、引き算を行うといった単純な計算のみを行っており、実際問題としてあり得ない時刻を入力した場合や、日をまたぐ作業時間の算出については今回説明に含めておりません。