●質問の主旨
時間を計測する場合、一方の日付が日をまたいだ場合
どのような関数を配置すれば時間のみを計測することが
できるでしょうか?ご存知の方ご教示願います。
●質問の補足
現在生活時間の実態調査をするため、
データベース形式で活動内容及び時間のデータを
収集しようと考えています。
ところが、「開始時刻」-「終了時刻」の差の時間を求めたときに、
「終了時刻」が翌日の「1:00」など日をまたいだ場合に
差を求められず#マークが表示されます。
#マークを表示させず時間のみを計測させるには
どのように関数式を記述させれば良いでしょうか?
(添付画像の関数式)
J5:=TIME(H5,I5,)
M5:=TIME(K5,L5,)
N5:=M5-J5
No.4
- 回答日時:
No.3さんに賛同します。
No.3さんの式を入力。次に、そのセルの書式を「ユーザー定義 h:mm」などに設定すると、時間として表示されます。
時刻が入力されているセルの書式を試しに「標準」などにしてみると、「22:10」は「0.923611111」と、「1:15」は「0.052083333」と変化します。これは、時刻のシリアル値が表示されている状態です。
このシリアル値は「24時間=1」とする小数です。「0.923611111」とは、「0.923611111日」のことです。ですから、No.3さんの式で「1」を足しているのは、24時間後の時刻に直すという意味です。
「#」が表示されている状態は、負の数に対して時刻などの書式が設定されていることを意味しています。Excelにとってみればエラーではなく、そのように表示することになっているだけです。ですから、単に時刻に足し算をすれば、24時間後に変更され、「#」でない時間が表示されます。
ただ既出の式では、計算結果が例えば「25時間」となるべき場合に、それより24時間少ない「1時間」となってしまう点が気になります。お示しのデータで、答えが24時間以上になるケースはあり得るのですか?
通常、データベースを作る際は、時刻だけでなく日付も記録することが多い気がします。そうであれば日付を式中に導入することで、計算ミスが出ないようにもできそうですが?
この回答への補足
MarcoRossiItaly様のご説明について。
>お示しのデータで、答えが24時間以上になるケースはあり得るのですか?
あまりないとは思いますが、「あり得ない」こともないと考えております。
>通常、データベースを作る際は、時刻だけでなく日付も記録することが多い気がします。
日付については別のセルに入力するつもりで、
特に関数式の引数にするつもりはありませんでした。
>そうであれば日付を式中に導入することで、計算ミスが出ないようにもできそうですが?
とすればどのような式が考えられるでしょうか?
もしご存知であればご教示願います。
たびたびの質問で誠に恐れ入りますが
よろしくお願い申し上げます。
MarcoRossiItaly様
ご回答ありがとうございます。
お礼が遅くなり恐れ入ります。
お時間がございましたら、
補足質問もご確認いただければ幸いです。
No.5
- 回答日時:
翌日が前日の時刻より大きくなる
例えば開始が5:10で終了が6:10となることがなければ
次の式で翌日の時間に1を加えて前日の時間を引くで
できます。
N5に =IF(M5<J5,M5+1-J5,M5-J5)
夜勤のアルバイトの給与計算
http://kokodane.com/tec3_5.htm
gyouda1114様
ご回答ありがとうございます。
お礼が遅くなり恐れ入ります。
上記のIF文参考にさせていただきます。
また合わせてURLも参考にさせていただきます。
No.6ベストアンサー
- 回答日時:
日付の導入について、ご質問にお答えします。
日付にも、時刻と同様、シリアル値が振られています。1900年1月1日(日)を1とし、その翌日が2、翌々日が3、…となっている自然数の数列です。
例えば、今日、セルに「3/12」と入力すると、Excelはこれを勝手に今年の日付だと判断してくれます。そのセルの書式によって、「日付」の書式「H24.3.12」とか、「ユーザー定義」の書式「3月12日」など様々に表示されます。入力後のセルにカーソルを置いていると、数式バーに「2012.3.12」などと表示されているので、数式バー内をクリックすることで他の年に編集することも可能です。
さて、2012年3月12日が入力されているセルの書式を「標準」にした場合、「40980」と表示されます。これがこの日のシリアル値です。つまり、1900年1月1日から数え始めて40,980日目であることを意味します。明日のシリアル値は1増えるので「40981」となります。
以上の説明と、「時刻は1日を1とする小数がシリアル値」ということを併せて考えると、どうしたらいいか見えてきたのではないでしょうか?例えば「2012年3月12日の23時」を1つの数で表すことができるのではないでしょうか?そう、足し算をすればいいですね?もしA1に「3/12」が、D1に「23:00」と入力されているならば、E1に「=a1+d1」と入力すれば、この瞬間の値が求められます。ただ、入力直後はE1の書式が時刻などに自動設定されたりするので、シリアル値を見るためには「標準」などに変更してあげましょう。「40980.95833」と表示されるので、1900年1月1日午前0時から40980.95833日後という言い方もできるでしょう。
あとは簡単ですね?上のようにして求めた2つの時点を表す小数どうしで、引き算するだけです。引き算を計算するセルに「時刻」の書式を設定しておけば、何時間何分という具合に表示されます。
また、この方法では、IFによる場合分けは不要で、とにかく引けばいいということになります。24時間でも48時間でも、1年後でも問題ありません。働きすぎにご注意(笑)
それから、日付の入力の仕方が上とは異なっている場合も説明しておきます。A1、B1、C1の3つのセルに「2012」、「3」、「12」と入力されていることにより2012年3月12日を表している場合です。その場合、DATE関数というのを使うと、日付のシリアル値に変換できます。
「=date(a1,b1,c1)」あるいは「=date(2012,3,12)」とセルに入力し、そのセルの書式を「日付」などにします。「標準」ではやはり「40980」と表示されます。D1とH1に時刻が入力されている場合なら、「=date(e1,f1,g1)+h1-date(a1,b1,c1)-d1」などと計算してもいいですね。
※Excelでは、文字列以外の式の入力において、大文字と小文字を区別する必要はありません。
MarcoRossiItaly様
かなりご丁寧な説明ありがとうございます。
特に「シリアル値」のご解説がよく分かりました。
アドバイス参考にさせていただきます。
助かります。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 指定した値以上の中で最小値を出したい 7 2022/10/24 21:12
- その他(Microsoft Office) エクセル 条件付き書式 日をまたぐ塗りつぶし 1 2023/01/13 18:00
- Excel(エクセル) エクセル 条件に合う日付に入力された時間数の合計したい 4 2022/06/17 22:18
- Excel(エクセル) Excel 2019で質問があります。 計測器のデータをExcelで記録したんですが、1秒刻みで記録 4 2022/09/07 22:46
- Excel(エクセル) TEXT関数(負の値)を集計のため数値に変換したい 5 2022/05/15 23:04
- その他(Microsoft Office) Excelで時間計算(負) 8 2023/02/26 05:47
- Excel(エクセル) エクセルの関数に関しての質問です。 5 2022/10/07 11:17
- Excel(エクセル) 出退勤管理の遅刻・早退時間について 3 2023/08/10 15:33
- Excel(エクセル) 【Excel】 開始時間からある日時までの経過時間を計算する関数を教えて下さい 5 2023/06/11 22:22
- その他(Microsoft Office) Excel 勤務時間の計算について~(残業)有.無しの場合。 4 2022/07/17 21:03
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
EXCELで○ヶ月を○年○ヶ月に変換...
-
リンク元の日付が空白の時リン...
-
Excelで「20080707」を『2007/0...
-
Excelのセルに数字を入れるが、...
-
Excel2013 表示形式で「1,000...
-
エクセルの曜日表示が正しく表...
-
斜線が入ったセルのカウント
-
【エクセル】数値にスラッシュ...
-
エクセルで日付を全角にしたい
-
エクセルでアポストロフィーが...
-
EXCELからCSVへ変換すると0が...
-
エクセルで9と入力したら時刻...
-
西暦から、曜日を表示⇒何も入力...
-
エクセルで「負の日付または時...
-
エクセルで関数のデータの後に...
-
エクセル 4ケタの数字を年月に...
-
エクセルの時間表示
-
エクセルで生年月日の数字のみ...
-
エクセル2003 2時間15分20.11...
-
EXCELで勝手に約分されてしまう
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
EXCELで○ヶ月を○年○ヶ月に変換...
-
リンク元の日付が空白の時リン...
-
エクセルの曜日表示が正しく表...
-
【エクセル】数値にスラッシュ...
-
Excel2013 表示形式で「1,000...
-
Excelで「20080707」を『2007/0...
-
Excelのセルに数字を入れるが、...
-
エクセルでアポストロフィーが...
-
斜線が入ったセルのカウント
-
EXCELからCSVへ変換すると0が...
-
エクセルで日付を全角にしたい
-
西暦から、曜日を表示⇒何も入力...
-
エクセルで9と入力したら時刻...
-
EXCELで勝手に約分されてしまう
-
エクセル 4ケタの数字を年月に...
-
Excel 時間の表示形式 セルと...
-
Excelのセルの書式設定(100...
-
EXCEL (0.5)の表示の仕方
-
エクセル 日付の表示を200...
-
Excel時間の入力方法
おすすめ情報