痔になりやすい生活習慣とは?

昼勤と夜勤で集計をしたいのです 夜勤が日をまたぐため集計の計算式がわかりません
昼勤8:30~17:30+残業で20:30まで
夜勤20:30~残業込みで8:30
SUMIFで集計するにも夜勤が0:00~になるので
どなたか数式を教えていただけないでしょうか?
よろしくお願いします

質問者からの補足コメント

  • うーん・・・

    ありがとうございます
    ちょっとむつかしいのでもう少し 教えてください
    時間     生産数
    1/20 20:40   100
    1/20 23:50 2568
    1/21 0:50 3245
    1/21 6:30 4563
    1/21 8:20 5556
    こんな感じですが勤務時間は1/20 20:30~1/21 8:30
    12hx24ということでしょうか?
    計算式等で教えていただけないでしょうか?
    よろしくお願いします

    No.1の回答に寄せられた補足コメントです。 補足日時:2018/01/28 16:31

A 回答 (4件)

No2で回答したものですが・・・


時間をまたいだ計算方法は次のようになりますね。
添付画像より・・・。
セルE2→ =C2+IF(C2-B2<0,1,0)-B2-"1:00"+D2 の式を入れます。
セルの書式設定、ユーザー定義より『 [h]:mm 』を入力。
数式の ”1:00” ←これは昼休憩の1時間です。

質問も、補足のコメントも理解出来ませんが、取り敢えず参考までに・・・。
「エクセルで日をまたぐ集計方法」の回答画像4
    • good
    • 0
この回答へのお礼

有難うございました
やっと解決しました
感謝です~

お礼日時:2018/01/28 20:32

日付と時間が別セルになっていれば、この画像のやり方で簡単に計算できます。

「エクセルで日をまたぐ集計方法」の回答画像3
    • good
    • 0

( ‥)ン?  セルの書式設定からユーザー定義で「h:mm」に設定するとどうですか?

    • good
    • 0

その時間がシリアル値(時分、或いは年月日時分)で記録されているならば、


その差分を24倍すれば、時間単位で表示されます。
更にその小数点以下を60倍すると、時間表示で余った分が表示されます。
この回答への補足あり
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aと関連する良く見られている質問

Qエクセルで日にちをまたぐ場合の時間計算

エクセルで表を作ってみて「あれっ」と思ったのですが、日にちを
またぐ時間データから、その期間の延べ時間を計算する方法って
あるのでしょうか?


6/10  6/11
22:00 9:58

上の区間の時間11:58を求めたいのですが、上手い計算方法があるので
しょうか?

Aベストアンサー

失礼しました
セルの書式について説明が抜けていましたね

24時間以上の時間を表示させるには、セルの書式設定の分類でユーザー定義を選び
次の書式を設定してください

 [h]:mm

[h] は24時間以上の時間をそのまま表示できるようにする記述方法です
これがないと、24時間を超える分は差し引かれて表示されます
ですので、#1のお礼にある 12:52 という表示は、実は 1日と12:52なんです
セルの表示形式でこの時間の部分だけを表示するように【指定】しちゃってるんですね

ちなみにどうでも良いことですが、書式を [m] とすると分単位で表示されます
覚えておいても損は無いと思います

Q日付をまたぐ時間計算について

いろいろ質問がありますが、自分のほしいものと一致する回答が見つけられなかったので教えてください。

エクセルで日付をまたいだ場合の時間計算(引き算)をします。
あるセル(A1)に"1"と入れるとB1にはvlookupで"1"を参照して、予定時刻を表示させます。D1には実際の時刻を入力し、それが予定よりも遅れていればE1に遅れた時間を計算させ(=D1-B1)、遅れがない場合は空白を返します。

予定時刻(B1)が23:30、実際(C1)は翌日00:30の場合
=C1-B1を入れるとD1は-23:00となりました。1時間の遅れなのでこのケースは1:00と表示させたいのですがどうしたらいいでしょうか。
vlookupで参照する元の予定時刻というのは時間だけで日付はありません。(飛行機や電車の時刻表をイメージするとわかりやすいかと思います。日付に関係なく毎日同じ時刻になっている、それと同じです。)
C1にはMM/DD_HH/MM形式で入力することもできますが、B1がHH/MM形式だから引き算ができないのでしょうか。B1,C1はそれぞれこの形式のままにしておきたいです。うまく説明できずにすみませんが、アドバイスよろしくお願いします。

いろいろ質問がありますが、自分のほしいものと一致する回答が見つけられなかったので教えてください。

エクセルで日付をまたいだ場合の時間計算(引き算)をします。
あるセル(A1)に"1"と入れるとB1にはvlookupで"1"を参照して、予定時刻を表示させます。D1には実際の時刻を入力し、それが予定よりも遅れていればE1に遅れた時間を計算させ(=D1-B1)、遅れがない場合は空白を返します。

予定時刻(B1)が23:30、実際(C1)は翌日00:30の場合
=C1-B1を入れるとD1は-23:00となりました。1時間の遅れなのでこのケ...続きを読む

Aベストアンサー

日付と時間はシリアル値として扱われているので、他の方が回答しているように時間だけを記入すると同じ日として扱われます。
シリアル値は一日で1、二日で2、というように1900年1月1日0時からのカウント値です。

時刻表ということですので、時間差が24時間以内であると仮定して
こんなのは如何でしょう。

 =C1-B1
   ↓
 =MOD(C1+"24:00"-B1,"24:00")
  または
 =MOD(C1+1-B1,1)

明らかにC1セルの時間がB1の時間より後になるのであれば、これで計算できます。
※MOD関数の詳細はExcelのヘルプを参照してください。

24時間以上の差を求めるのであれば素直に日付まで指定して計算することをお奨めします。
D1のセルの表示形式は「 [d] hh:mm 」で良いでしょう。
4日後を指定するのであれば次の式でも可能です。
E1セル=4
 =C1+E1-B1

Qエクセル2010 深夜残業時間の計算

出社・退社の時間から、残業、深夜残業など、
残業時間を算出する数式を作成していますがどうにもうまく出来ません。

残業となる時間の切り分けは、平日は以下のようになっています。、
 基本時間→8:30~17:15
 残業→5:00~8:30 と17:15~22:00
 深夜残業→22:00~翌朝5:00
 休憩時間→0:45(C1) 基本勤務時間→8:00(B1)

例えば平日、朝6:00出社→23:00退社であれば、
早朝残業が2:30、通常残業が4:45、計7:15です。深夜残業が1:00となります。

F欄に時間外、 G欄に深夜残業としています。
現状は時間外を出すのに  =SUM(E5-D5)-$B$1-$C$1-G5 と単純にしてます。(G5は手入力)
これを自動で入力できたらと考えています。
5:00前に出勤することはほとんどありませんが、22:00を超えることはあります。
ただし、24:00を超えると翌日計算になります。

過去欄を参考にしようにも理解できなく困っています。
http://oshiete.goo.ne.jp/qa/8147465.html
よろしくお願いいたします。

出社・退社の時間から、残業、深夜残業など、
残業時間を算出する数式を作成していますがどうにもうまく出来ません。

残業となる時間の切り分けは、平日は以下のようになっています。、
 基本時間→8:30~17:15
 残業→5:00~8:30 と17:15~22:00
 深夜残業→22:00~翌朝5:00
 休憩時間→0:45(C1) 基本勤務時間→8:00(B1)

例えば平日、朝6:00出社→23:00退社であれば、
早朝残業が2:30、通常残業が4:45、計7:15です。深夜残業が1:00となります。

F欄に時間外、 G欄に深夜残業としています。
現状...続きを読む

Aベストアンサー

深夜部分の残業時間を計算するなら、以下のような数式で早朝(5:00以前)部分と夜間(22:00以降)部分の深夜残業を足し算するのが簡単です。
(退出時間が翌日になる場合は25:00のような形式で入力した場合)。

=5/24-MIN(D5,5/24)+MAX(E5,22/24)-22/24

退出時間を「25:00」ではなく通常の「1:00」のように入力する場合は、以下の式で計算することができます。

=5/24-MIN(D5,5/24)+MAX((E5<D5)+E5,22/24)-22/24

ところで、深夜部分がメインの勤務がある場合、休憩時間などは考慮しなくてもよいのでしょうか?

QEXCELで夜勤の合計時間を出したい!

EXCEL初心者です。
夜勤の勤務時間の合計を出したいのですが、うまくいきません。
夜の8時(20時)から日を跨いで1時半までの勤務の合計(5時間半)という計算をしたいのですが、どのような計算式を入れたらいいでしょうか?
どうかよろしくお願い致します。

Aベストアンサー

日をまたがる時刻どうしを計算させる場合、日またがりの時刻は 25:30 のように入力するほうが簡単になります。セルの書式は [h]:mm にしておけばよいです。
これならA1に「20:00」、B1に「25:30」でC1に式「=B1-A1」で「5:30」という結果が得られます。

なぜかというとエクセルの内部では1日はシリアル値の「1」で管理され、1時間はその1/24である「0.041666667」で表されます。
ですから日またがりのつもりで「1:30」を入力すると、ただの「1:30=シリアル値0.0625」とみなしてしまうからです。(25:30=シリアル値1.0625)

もちろん日またがりの時刻を「1:30」と入力して
 =IF(A1>=B1,B1+1-A1,B1+A1)
でも「5:30」を得ることができますが、休憩時間などを考慮すると式が複雑になる可能性があります。(式で1を加算するのはシリアル値の「1日」相当の値です)

Qエクセル 深夜勤務時間の算出方法

こんにちは。

今、会社でエクセルを使った勤務表を作成しています。
出社時間、退社時間をそれぞれセルに入力すると、深夜勤務時間のセルに夜22:00から朝5:00の間に勤務した時間を抽出して表示したいと思っています。

例として、出社時間を「20:00」、退社時間を「3:00」と入力した場合、深夜勤務時間に「5:00」という風に計算して表示させたいのです。

深夜勤務時間にはどういった計算式をいれればいいでしょうか?
よろしくお願い致します。

Aベストアンサー

昔私も、タイムカードの集計と手書きの集計を合わせる為に
エクセルで表を作った事がありますが・・・・
タイムレコーダーが特殊な計算方法を取っていた事と
勤務時間帯が昼夜問わずだった事、深夜勤務時間帯は手当がつくので分ける事、
昼休みは基本的に1時間引く事になっているが、昼休みを取らないで
働いたりする事があった事などなど・・・諸事情により
もっともっと面倒臭い事になりました(苦笑)。

IF関数による場合分けが必要です。
(1) 出社時間、退社時間ともに深夜にかからない場合
(2) 出社時間が深夜にかからないが、退社時間が深夜にかかる場合
(3) 出社時間が深夜にかからないが、退社時間が深夜を超えて(5時以降)いる場合
(4) 出社時間、退社時間ともに深夜時間帯の場合
(5) 出社時間が深夜で、退社時間が深夜を超えて(5時以降22時以前)いる場合
(6) 出社時間が深夜で、退社時間が昼間時間を超えて(22時以降)いる場合

つまり、深夜勤務時間は
(1)なら【0】であり、
(2)なら退社が23:59までは【退-出】、0:00以降なら【退+24:00-出】、
(3)なら【7】、
(4)なら出社が23:59までの場合、
退社が23:59までは【退-出】、0:00以降なら【退+24:00-出】、
出社が0:00以降の場合は【退-出】、
(5)なら出社が23:59までの場合、【29:00-出】、
出社が0:00以降の場合は【5:00-出】
(6)なら合算
といった感じです。どうです、面倒臭いでしょう・・・。
(というか、場合分けが多過ぎてIFの7段の限界を超えているような・・・。)
私は、昼間の勤務時間をIF関数6段重ねで算出し、深夜の時間帯は
総勤務時間から昼間の時間を引くような形にしてました。

ですから、やはり他の方がおっしゃるように27:00と入力するのが
一番面倒がないように思いますよ。
入力時に3:00を27:00と計算するのが面倒臭いというのであれば、
出社時間>退社時間なら退社時間に24時間足すセルを用意して
入力セル(「3:00」と入力する)と
退社時間の結果表示セル(「27:00」と表示させる)を分け、
退社時間は結果表示セルから引っ張るようにすれば良いように思います。

昔私も、タイムカードの集計と手書きの集計を合わせる為に
エクセルで表を作った事がありますが・・・・
タイムレコーダーが特殊な計算方法を取っていた事と
勤務時間帯が昼夜問わずだった事、深夜勤務時間帯は手当がつくので分ける事、
昼休みは基本的に1時間引く事になっているが、昼休みを取らないで
働いたりする事があった事などなど・・・諸事情により
もっともっと面倒臭い事になりました(苦笑)。

IF関数による場合分けが必要です。
(1) 出社時間、退社時間ともに深夜にかからない場合
(2) 出...続きを読む

Qエクセルの深夜時間計算について

エクセルを使って勤務時間表を作っていますが
22:00から29:00までが深夜時間になっていて その計算方法がわかりません
例1出勤-退勤-残業-深夜
  10:00-25:00-7,0-3,0
例2 3:30-19:30-8,5-1,5
深夜時間計算のみ教えていただきたくお願い致します。

Aベストアンサー

こんにちは。maruru01です。

A2に出勤時間、B2に退勤時間を入力する場合、深夜時間のセルに、

="5:00"-MIN("5:00",A2)+MIN("29:00",B2)-MIN(MAX("22:00",A2),B2)

と入力します。
質問の例だと、それぞれ「3:00」「1:30」と表示されます。(表示形式は時刻にします。)
時間数で表示する場合は、数式を24倍して、表示形式を標準にして下さい。

Qエクセルで表示形式の時刻の「0:00」を表示しないようにするには?

エクセルで残業の計算をしています。
入社時間 退社時間 残業時間
のような感じでしているのですが、
残業時間が、0:00(つまり残業0)の場合は、
何も表示しない場合は、どのような関数を書けばよいでしょうか?

ちなみに残業時間のセルには、残業時間を自動的に表示させるために、
すでに「=IF((A1="")+(B1=""),"",B1-A1-"8:30")」という式が入っています。
この式にどういう式を付け加えればよいのでしょうか?

Aベストアンサー

ただ単に表示形式を変更すればいいです。現状で書式が時刻形式の
h:mmになってると思いますので、ユーザー定義でh:mm;;と後ろにセ
ミコロンを2つ追加して下さい。負の値と0の時は何も表示しないと
いう意味になります。

QEXCELで日付と時刻が入ったセルを日付だけ取り出したい

(例)

 2005/9/10 13:10:23

が入ったセルが有ります、使いたいのは日付だけ
 なんですが、どうすればできるのか?

 ※結果は別のセルに成っても良いし、元のセルでも可

 宜しくご教授お願いします。

Aベストアンサー

私も単に書式を変えるだけでいいと思いますが、
時間部分のデータを削除したいということであれば、
時間部分は、小数データ部分なので、
=INT(A1)
とかしてやれば時間データをクリアすることができます。
その後書式の設定をしてやって下さい。

Q日またぎにも対応した勤務時間表を作りたい(やや長文)

現在、Excelで勤務時間表を作っています。
使用者が始業時刻と終業時刻を入力すると後は全て計算で出るように作っています。
誤入力防止の為、入力規則で0:00~23:59しか入力出来なくしています。
(深夜1時は25時ではなく1時と入力)
通常時間帯と深夜時間帯では手当が異なるのでそれぞれの勤務時間を算出します。
深夜時間帯は22:00~6:00です。
早く出勤した場合の早出はMAX(6:00-[始業時間])で対応出来ました。
日をまたがない場合はMAX([終業時刻]-22:00)で対応出来るのですが
日をまたいだ場合、深夜時間帯の労働時間算出がうまく出来ません。
誤入力を考慮せず25:00と入力させた時はうまく出来ました。
(終業時刻入力形式を0:00~6:00は入力制限していた。
但し、整数を入れるとそのまま24:00、48:00と表示されてしまったため使用中止)
現在の入力形式のままでうまく機能させる式、
或いは、終業時刻の入力をうまく制限する方法がありましたら教えて下さい。

Aベストアンサー

こんにちは~

始業時刻が、22:00以降ということはないのでしょうか?
また、深夜残業の終了時刻が 6:00 を超えたらどうするのでしょうか?

そういうことは考慮しなくてもよければ、
「始業時刻」が A2、「終了時刻」が B2 だとして、

=MAX(B2+(A2>B2)-"22:00",0)

で試してみてください。

QExcelでの勤務表作成 *夜勤がある場合の計算式

Excelで勤務表を作成しているのですが
関数の作成に困っています。

私の職場には日勤(9:30~18:00)と夜勤があります
夜勤は 宿直(17:00~24:00)、宿明け(0:00~10:00)
の時間帯です。

セルA 開始時間
セルB 終了時間
セルC 休憩時間
セルD 稼働時間
セルE 所定時間
セルF 時間外残業

セルDの稼働時間を求める関数に
=IF(OR(A1="",B1=""),0,B1-A1-C1)

セルEの所定時間の関数を
=IF(D1>$N$44,$N$44,D1)  としました。*N44には7:30を投入

この場合は日勤の場合の勤務時間は正しく反映されますが
夜勤(宿直、宿明け)の勤務時間を投入した場合
時間外残業が正しく反映されません。

所定時間を日勤の場合は7:30(7時間30分)
宿直の場合は6:00(6時間)
宿明けの場合は9:00(9時間)
と設定すればうまくいくと思うのですが
どのように設定したらよいでしょうか?

Aベストアンサー

開始時間で判定するなら
E1=MIN(D1,LOOKUP(A1*24,{0,9.5,17},{9,7.5,6})/24)

上記で開始時間の範囲で
00:00~09:29→宿明 9時間
09:30~16:59→日勤 7時間30分
17:00~23:59→宿直 6時間
が判定値になります。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング