- ベストアンサー
午前0時をまたがっての時刻計算
エクセルにて、一日の出勤、帰着などの時刻を記録し、データー加工していますが午前0時をすぎる日があり、うまく処理できません。 列の項目 入力は、出発時刻、帰着時刻。 計算して表示する列は、出発から帰着までの時間、9:30を業務開始時刻としての就業時間、目標出発時刻7:30より実際の出発時刻のずれ+-、目標帰着時刻22:30より実際の帰着時刻のずれ(分)+-です。 行のほうは、 1ヶ月の日にちごとのレコード、合計、平均、最大、最小です。 レコードには、休日は入力しないのでNULL値になっています。これを0としないことが、平均出すために必要です。 いま、起こってる現象としては、目標との差が30分のはずが1410分と表示されたりしてしまう。 とにかく深夜0時をこえるレコードがあるため、うまくいきません。 平均も夜23時と、深夜1時の平均は真夜中0時となってほしいのに正午になってしまいます。 一番遅い帰着時間の日は、午前2時の日になるべきところが23時59分の日になってしまいます。一番早い帰着時間も同様、午前0時過ぎの日になってしまいます。
- yuusukeccc
- お礼率70% (209/297)
- オフィス系ソフト
- 回答数4
- ありがとう数4
- みんなの回答 (4)
- 専門家の回答
質問者が選んだベストアンサー
エクセルの表示形式は、普通に時刻を入力すると、"h:mm"になってしまいます。この場合、24時間、60分、60秒を超える表示はできません。hを[]で囲んで"[h]:mm:ss"と表示形式を変更すると、24時間を超える時間を表示できるようになります。ご質問の件の夜23時と、深夜1時の平均が、真夜中0時(24:00)と表示されるはずです。 詳細は以下のURLなどを参照してみてください。
その他の回答 (3)
- NCU
- ベストアンサー率10% (32/318)
0時過ぎたら1を足せ、という諺がありますよね。(ないか) 入力で対応してもいいし、数式で吸収してもいいと思います。 >目標との差が30分のはずが1410分と表示されたりしてしまう。 24*60-30=1410 まさに1日分のズレですね。
お礼
今まで作った計算式は、No1.の方の補足の所に書いておきました。”24:00”を足すという数式は、入れて合ったのですが、アドバイスにしたがって「1」に変えてみました。でも、結果は変わりません。 ありがとうございました。
- zap35
- ベストアンサー率44% (1383/3079)
>とにかく深夜0時をこえるレコードがあるため、うまくいきません。 >平均も夜23時と、深夜1時の平均は真夜中0時となってほしいのに正午になってしまいます。 エクセル内部では1時間が1/24の値に換算されます。 23:00は23/24、01:00は1/24ですから平均を取ると12/24=昼の12時となってしまいます。 ですから24時を越える場合は数字の1を加算してやらなければなりません。深夜1時は01:00に1を加えて25/24、これと23時の平均を取ると深夜0:00になります。 でもこのような処理をEXCELで実現する場合、「何を以て日またがりと判断するか」は結構難しい問題です。(07:00は当日の7時? 徹夜明けの7時? など) ですから場合によっては25:00、27:00などの表記をした場合が計算しやすい場合もありますので検討されてみてください
お礼
深夜0時を越えるデーターが入る列は決まっておりこの列には、逆に午後5時以前のデーターは入ることはありません。今は、AM9:30を基準に判定を考えましたが、AM7時でも正午でも大丈夫だと思います。 ありがとうございました。
- wisemac21
- ベストアンサー率39% (171/429)
実際に使用しているデータ表と数式を提示しないと、回答がつかないと思います。 提示があれば数式の訂正等具体的に回答されるはずです。
補足
エクセルをこの「教えてGOO」に添付する方法がわからず、遅くなってしまいました。 セル連結などしてあるわかりにくい表なのですが。 E列:出発時刻の入力 例8:01:00 画面上の表示は秒は表示していません。 J列:帰着時刻入力 例1:53:00 深夜0時を越えるデータ入力があるのはこの列だけです。 C列:出発時刻の目標(8:00)よりのずれ。早いほど+評価。 M列:数式 =IF(E9="","",IF(J9<"9:30","24:00"-"9:30"+J9,J9-"9:30")) 9:30から帰着までの時間を仮想勤務時間として計算。休日は、入力しないのでnullです。0にしてしまうと平均値が狂うのでわざと判定を入れています。 ここまでは、正しく計算されています(という気がします)。表示形式はh:mm。ただ、ここの”24:00”が曲者のような気もしています。 H列:数式=IF(E9="","",(M9-"13:00")*1400) 帰着時間の目標(22:30)からのずれ。 帰着時間が0時前のときに1440を超える異常値が出ます。 Q列:仮想勤務時間の累積です。 数式 =IF(E8="",0,M8*1440) 次の行は、=IF(E9="",Q8,Q8+M9*1440) ここも帰着時間が0時前のときに異常値が加算されていってるような... 各列に平均、最大、最小を出す数式を入れた行を作っていますが、はっきり言って得られる結果はほとんどむちゃくちゃです。 数式の例 =AVERAGE(E8:E38) =MAX(E8:E38) =MIN(E8:E38) このE列は、まともですが、他は... こんなのでわかりますか?
関連するQ&A
- エクセルでの時間計算方法
毎日ドライバーが色々な時間に車の運転をします。出発時刻と帰着時刻だけがわかっているので それをもとに17時10分以降翌日の7時40分迄の間のみ運転している時間を計算したいのですが どういう計算式をいれればいいですが? 例えば 8/1出発10時帰着15時だと 0時間 8/2出発18時帰着20時だと 2時間 8/3出発 5時帰着 8時だと 2時間40分 というような答えがほしいのですが・・・。
- 締切済み
- オフィス系ソフト
- Googleカレンダーでの午前時刻入力について
Googleカレンダーの「月」ビューで日付をクリックして予定を登録する時、 タイトルに "4:00 早朝ランニング"などと入力し、予定作成ボタンを押すと、 予定時刻が午前4時ではなく、夕方の4時(16:00)として登録されてしまいます。 午前4時として登録するには、タイトルに予定時刻をどのように入力したらよいのでしょうか?
- ベストアンサー
- Googleサービス全般
- 車輌運行表にて出発時メーターを入れると前日の帰着時メーターが自動で入力されるには?
教えて頂きたいのですが、 例) 出発時km 帰着時km 1日 12000 12050 2日 12050 12095 3日 4日 12095 12150 5日 6日 12150 上記のような運行表を月末に作成しているのですが、出発時のメーター数を入力した時点で前日の帰着時のセルに同じ数字が入力されるようにするには、どういう関数を使用すれば良いのでしょうか?毎日乗るのなら簡単なんですが、入力しない日(空白の列)が出来るとどうやればいいのか分かりません。教えて下さい。お願いします。
- ベストアンサー
- オフィス系ソフト
- 【Excel】時間計算について
Excel2003を使用しています。 残業時間の計算について、下記のような場合、F2とG2には、どのような数式を入力すればいいでしょうか? 22時以降は深夜残業となりますので、終了時刻が22時を過ぎた場合は、その部分の時間をG列の深夜残業に表示し、22時まではF列の残業に表示させたいのです。表示形式は、C列とE列は『時刻』、F列とG列は『h"時間"mm"分"』になっています。 C列 E列 F列 G列 1 開始時刻 終了時刻 残業 深夜残業 2 17時30分 23時30分 4時間30分 1時間30分 IFで条件分岐して入力してみたり、過去の質問も検索してみましたが、思うように表示させることができませんでしたので、質問させていただきました。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- Excel入力データの時刻表記を統一したい
エクセルで、時刻が入力されている列があるのですが、入力形式が様々で困っています(現時点で6パターン確認されています)。 これを ・分単位まで ・4桁 ・48時間表記(24時以降も翌日24時までは同一日として扱う) ・テキスト形式 の1パターンに表記統一したいと思っています。(例:午前4時30分なら、"2830"にしたい) 現在の確認されている6パターンは以下のとおりです。 1.正しく4桁48時間表記になっているもの(直す必要のないもの) 例:午前4時30分の場合に"2830" 2.3桁以下になっているもの 例:午前4時30分の場合に"430"、午前2時の場合に"2" 3.48時間ではなく、24時間表記になっているもの 例:午前4時30分の場合に"0430" 4.時と分の間に:が挟まっているもの 例:午前4時30分の場合に"04:30" 5.秒まで入力されていて、時と分と秒の間に:が挟まっているもの 例:午前4時30分の場合に"04:30:00" 6.テキストではなく、時刻のシリアル値で入力されているもの 例:午前4時30分の場合に"0.1875" 入力形式を統一してもらえればそれが一番なのですが、今回は既に入力されたデータを使って分析をしないといけないので。良い方法があれば教えてください。 宜しくお願いいたします。
- ベストアンサー
- その他MS Office製品
- 皆様の通勤時間(時刻)・起床・退社時刻などを教えてください
似たようなアンケートがございますが、 より細かく教えていただければと思います。 現在私は、通常勤務時間は8時~17時までになっています。 実際の現状は、 業種→福祉 勤続年数→2年 起床時刻→朝5時 自宅出発時刻→6時 通勤時間→1時間20分 会社到着→7時20分 退社時刻→19時30分 帰宅時刻→21時 就寝→0時 のように平均してなっております。 これは普通なんでしょうか? 私的には朝5時起床は苦痛です(汗; 皆様の起床時刻や通勤時間、業種などを 教えていただければ幸いです。
- 締切済み
- アンケート
- EXCELで24時間表記を午前・午後表記に変えたい
A列に、12:00、14:00、19:00、23:00……と午後の時刻を24時間表記されたデータがあります。 これをB列に、0:00、2:00、7:00、11:00のように「PM」や「午後」などの文字がいっさい付かない時刻数字だけの12時間表記に変えたいのですが、その方法が分かりません。 表示形式の時刻には必ず後ろに「PM」が付くし、ユーザー定義で「PM」などを付けないで時刻だけで12時間表記にすることをいろいろ試してみましたがどうしてもできません。 午前の時刻は「AM」など付かないで表わせるのに、午後はどうして「PM」がつくのかも分かりません。 表示形式で出来なければ、何らかの関数式でB列にそのような表記をする方法はないでしょうか。 データが多いので、いちいち入力し直すのは大変なので、ご存知の方教えてください。
- ベストアンサー
- その他MS Office製品
お礼
アドバイスにしたがって、表示形式を[h]:mmにして見ましたらうまくいきました。 データーがそのままではうまくいきませんが、1:09→25:09のように入力しなおして見ましたらばっちりです。25時とか26時とかは慣れていないので、入力に戸惑います。1時と入れると25時と変換されるように工夫してみたいと思います。 ありがとうございました。