丸め時間や切り捨てはできたのですが時間振り分けが出来なくて困ってます。
あと通常時間のIF関数も他からの引用でどうにか出来ました。
どなたか残りの部分を教えてください。

  A      B     C      D      E      F     G
1 出勤時間   退勤時間  実働時間   通常時間   深夜時間   残業時間  分給
2 18:00  5:05  11:00  4:00   4:00   3:00  0:05
3 16:00  4:13  12:00  6:00   2:00   4:00  0:13
4 17:00  5:31  12:30  5:00   3:00   4:30  0:01
5 23:00  5:25   6:00  0:00   6:00   0:00  0:25



上記の様に開始時間や終了時間がバラバラです。

通常時間は22時までの計算です。
深夜時間が8時間ー通常時間です。
残業時間が実働時間ー通常時間ー深夜時間になります。
分給が30未満でつきます。(4の列のように31分だと1分つきます)

基本はAとBに時間を入力するだけで残りが自動で打ち込まれるようにしたいと思ってます。

大変面倒くさいとは思いますがよろしくお願いします。

A 回答 (5件)

なるほど。

夜の22時~朝の6時までの内、出勤時間から8時間以内のものが、深夜時間となるわけですね。
21時に出勤して最大の15時間働いたとすると、
通常時間:21~22時→1時間
深夜時間:22~6時の内21時+8時間=5時まで→7時間
残業時間:5時~12時→7時間
ですね。
0時出勤で15時間だと
深夜時間:0時~6時→6時間
通常時間:6時~(6時+(8時間-6時間=2時間)=8時)→2時間
残業時間:8時~15時→7時間
ですね。

深夜時間となる時間帯が8時間で、残業でない時間も8時間なので、場合わけが少なくて助かります。
開始時刻が深夜時間帯ではなかった場合、通常時間のみor通常時間+深夜時間
開始時刻が深夜時間帯だった場合、深夜時間のみor深夜時間+通常時間
というわけですね。

「今の式」ですと、23時開始で8時間働いた場合も通常時間が0となりますね?
しかし実際は6時~7時の1時間は通常時間のはずです。

A:16時以降1時まで
B:最大でAから15時間後
C:AからBまでの時間を30分単位で切り捨てたもの
D:AからBまでの内、22時以降6時までを除く、A+8時間以内のもの
E:AからBまでの内、22時以降6時までので、A+8時間以内のもの
F:AからBまでの内、A+8時間以降のもの
G:Cによって切り捨てられたもの。最大でも30分未満

D2=IF(A2<22/24,IF(A2<6/24,IF(B2>6/24,MIN(B2,A2+8/24)-6/24,0),IF(B2>22/24,IF(A2>B2,MIN(22/24,A2+8/24)-A2,MIN(B2,A2+8/24)-A2),MIN(IF(B2<A2,22/24,B2)-A2,8/24))),IF(B2>6/24,MIN(B2,A2+8/24)-6/24,0))
でしょうか。22時以降と6時以前をA,Bそれぞれ比較し、AとBがどちらが先か(日をまたいでいるか)を確認し、BがAから8時間以上であるかどうかも確認しているので、非常に分岐が多いです…
思った値が出ているか確認してもらえればと思います。

…と思ったのですが、これは30分丸めができてないですね。
また明日にでも考えてみますが、とりあえず仮保存代わりに置いておきます。
    • good
    • 0
この回答へのお礼

おはようございます。
用事で出かけてた為お礼も遅れてしまい申し訳ありません。

出勤時刻を丸めた列を新しくC列に追加してAのセル指定をCへ変更しました。

そうすると出来た?感じです。
重ね重ねご親切な説明と式まで考えて答えて頂き大変ありがとうございます。

お礼日時:2017/04/16 09:47

勤務時間が早くても16時以降というのは分かりましたが、


その情報だけでは、遅くても何時までに出勤するのかが分からなければ同じ事ですよ?
16時に出勤する人よりも17時に出勤する人の方が1時間遅い、
17時の人より18時の人、23時の人より0時の人、0時の人より1時の人・・・
仮に0時の人が最大の15時間働いたとして、深夜時間は何時間なのでしょう?
少なくとも0時~5時が深夜時間であるのは、例の5行目で分かっていますが、何時まで深夜扱いなのかはわかりません。

=IF(B2<A2,B2+1,B2)-A2-C2
これは、「30分で切り捨てしていない状態の実働時間」から、実働時間(C2)を引く式ですよね?
先ほどG2の例で出したものです。
コピーミスでしょうか?
    • good
    • 0
この回答へのお礼

おはようございます。
昨日で返事ができずにすみません。

遅くての出勤時間は特にきまっていませんが今のながれだと25時(1時)になると思います。

式はコピーミスです。
今の式は「=IF(A2>TIME(22,0,0),0,("22:00"-MAX(A2,"16:00")))」です。

説明不足で補足させてください。

通常時間(16時~22時)
深夜時間(22時~30時)
残業時間(出勤して8時間を超えた場合のみ)

18時に出勤して3時に退勤すると
通常時間4(~22時)
深夜時間4(22時~2時)
残業時間1(2時~3時)
となります。
通常時間+深夜時間=最大8時間です。

せっかく教えて頂いたのですがEの式がかわるかと思います。
ここまで親切に教えてもらってるのに甘えてばかりで申し分けありません。

お礼日時:2017/04/15 10:02

結局どこの計算はできて、どこの計算が残っているのでしょうか?



出勤時間:時刻を入力

退勤時間:時刻を入力

実働時間:(退勤時間-出勤時間)を30分未満切捨て
C2=ROUNDDOWWN((IF(B2<A2,B2+1,B2)-A2)*24*2,0)/24/2

通常時間:22時までの実働時間
※開始時刻の指定がありません。
少なくとも22時~5時は全て深夜であるのは分かります。
例えば、5時に出勤して12時まで働いたとすれば、通常時間と深夜時間はそれぞれ何時間なのでしょう?
5時に出勤する可能性が0なのであれば、6時でも7時でもかまいません。
要は、式で判断する為の基準が必要なだけです。
なので式は省略します。

深夜時間:実働時間が8時間を超えているなら、8時間-通常時間、そうでないなら、実働時間-通常時間
E2=IF(C2>8/24,8/24-D2,C2-D2)

残業時間:実働時間が8時間を超えているなら、実働時間-8時間、そうでないなら、0
F2=IF(C2>8/24,C2-8/24,0)

分給:実働時間算出時に切り捨てられた30分未満の時間
G2=IF(B2<A2,B2+1,B2)-A2-C2


D2以外は成立していると思いますので、D2の条件だけ追加情報をお願いします。
ただし、24時間以上連続勤務の可能性があるとかであれば、時間でなく日時の入力とし、式も修正が必要になります。
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。

式まで書いて頂き大変ありがとうございます。
例だったのでコピーで引用してセルの指定を変えるだけで使えました。

開始時間ですが早くて16時からで特にきまってません。
最大でも15時間までの勤務になります。

一応今のところD2の式は「=IF(B2<A2,B2+1,B2)-A2-C2」で入力しています。
これより的した式があれば教えて頂けるとありがたいです。

本当にお手数おかけします。

お礼日時:2017/04/14 20:21

AやBは、年月日時分の入力としてください。


でないと、A<Bとはなりません。

C以降は、時間単位の表示が良いと思います。
終業区分は貴殿の定義により、
実働時間=退勤時間-出勤時間
通常時間=max((出勤日の年月日+22時)-出勤時間,0)  
深夜時間=min(8時-通常時間、通常時間)  ※
残業時間=max(実働時間-通常時間-深夜時間、0)

上記例では、30分単位丸め処理は考慮されていません。
※ 通常時間が8時間を超えるとどうなるのでしょうか?
 この対策はされていません。

C以降の時間表示は、以下の方法があります。
時:分 …分は端数のまま。時給計算で30分単位にまとめる
時:分の30分単位丸め表示 …ご質問例の通り
時間単位 …計算結果を24倍すれば時間単位になる。
 30分単位は0.5丸めに相当する。

その他、
休憩時間がありません。この例では労基法違反です。
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。

説明不足で申し分けありません。
通常時間は16~22なので8時間を超える事がありません。

休憩時間は決まってないですが細かくとれてますので大丈夫です。
労基法の事まで考えて頂きありがとうございます。

お礼日時:2017/04/14 20:15

自分はこの手の処理については、時間ごとに区切って考えるようにしています。


 通常勤務時間の休憩前、
 通常勤務時間の休憩後、
 時間外勤務、
 深夜勤務時間の休憩前、
 深夜勤務時間の休憩後、
このように分けて考えます。

通常勤務時間の休憩前であれば、
 出社時間と通常勤務の開始時間を比較し、大きいほうの時間を選択し、
  MAX("8:30",A2)
 退社時間と休憩時間を比較し、小さいほうの時間を選択し、
  MIN("12:00",B2)
 引き算を行う。
  =MIN("12:00",B2)-MAX("8:30",A2)
これで通常勤務時間の休憩前(午前中)の勤務時間を求められます。

このようにして他の時間も計算し、それを元に
「実働時間」「通常勤務時間」「残業時間」「深夜残業時間」などを求めます。

細かく区切ることでタイムテーブルを埋めると考えれば良いでしょう。
↓こんなイメージ(具体的な時間は自身で調整してください)
「エクセル 不定期勤務について教えてくださ」の回答画像1
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。
そういう風な考え方を持ってませんでした。
大分参考になります。

イメージまで作って頂き大変ありがとうございます。

お礼日時:2017/04/14 20:11

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

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

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

Q条件付き書式 時間 0:00:00の場合

セルの値を差し引いて、時間を出してるのですが
その時間が「0:00」の時、
そのセルを灰色にしたいのですが、
条件付き書式でうまくできません。

「=$A65536="0:00:00"」
「=$A65536="0:00"」
でも、何も変わりません。

時間は、セルの書式設定で「h:mm」にしています。

Aベストアンサー

どれでも。

=$A65536=0
=$A65536-"0:00"=0
=$A65536="0:00"+0
=$A65536="0:00"-0
=$A65536="0:00"*1
=$A65536="0:00"/1
=$A65536="0:00"^1
=$A65536=--"0:00"
=$A65536=n("0:00")
=$A65536=value("0:00")
=$A65536=timevalue("0:00")
=$A65536=sum("0:00")
=$A65536=max("0:00")
=$A65536=min("0:00")
=$A65536=average("0:00")
=$A65536=median("0:00")
=$A65536=imreal("0:00")
=text($A65536,"h:mm")="0:00"
=delta($A65536,"0:00")

最もよく使われているのは多分「*1」だと思いますが、「0:00」という時間に限って言えば、いちばん上の式も十分に分かりやすいです。

なお問題になっているのは、セルに記入されている(あるいは算出されている)値が、数値なのか、それとも文字列なのかという点です。セルに数値があって(あるいは空白で)、それを「0:00」という文字列と比較しようとしても、そのままでは当然、一致しません。Excel にデータ型を自動変換させるため、上のような数式が必要になっています。

したがって、セルの書式は何であっても構いません。書式は見かけを設定するだけのことなので、値とは直接の関係がありません。

どれでも。

=$A65536=0
=$A65536-"0:00"=0
=$A65536="0:00"+0
=$A65536="0:00"-0
=$A65536="0:00"*1
=$A65536="0:00"/1
=$A65536="0:00"^1
=$A65536=--"0:00"
=$A65536=n("0:00")
=$A65536=value("0:00")
=$A65536=timevalue("0:00")
=$A65536=sum("0:00")
=$A65536=max("0:00")
=$A65536=min("0:00")
=$A65536=average("0:00")
=$A65536=median("0:00")
=$A65536=imreal("0:00")
=text($A65536,"h:mm")="0:00"
=delta($A65536,"0:00")

最もよく使われているのは多分「*1」だと思いますが、「0:00」という時間に...続きを読む

Q7桁の時刻(00:00:00.0)のコンマ以下を省略する方法を分かる方教えて下さい!

エクセル初心者につき、教えて下さい!

7桁の時刻のコンマ以下を省略したいのですが、その方法を分かる方、教えて下さい!
例 18:34:32.5→18:34:32

Aベストアンサー

No.6です。
まだ解決していませんでしたか。

四捨五入ではなく切り捨てにしたいのであれば、
=TEXT(A1-"00:00:00.5","hh:mm:ss")*1
のように、丸めたい下の桁から5引いてから四捨五入されるようにするとよい。

TEXT関数で、数値に対して書式を指定して表示させ、1を掛けることで数値(シリアル値)に戻しています。
数式を入力したセルの表示形式をユーザー書式で "h:mm:ss" または "hh:mm:ss" にしてください。

Qエクセル:関数で時間「0:59」を「1:00」にしたい

お世話になります。
急ぎの用務です。お助けください。
よろしくお願いします。

◆時間表示の切り上げを30分単位でしたいのです。
 関数でできますか?ご教授お願いいたします。

例)
「0:59」→「1:00」
「1:29」→「1:30」
「1:15」→「1:30」

よろしくお願いいたします。

Aベストアンサー

こういうこと p(..)でしょうか?

=CEILING(A1,"0:30")

セルの書式設定~表示形式を「時刻」に。

Qあるセルに17:00と表示されてるのに、編集時に(セルをダブルクリックすると)5:00:00PMと表示されるには?

エクセル(97)のあるセルに「17:00」と
表示されてるのに、編集する時にセルを
ダブルクリックすると「5:00:00 PM」
と表示される事があるのですが、
これはどうやってるのでしょう?

表示形式を変えてしまうと、表示そのものが変わってしまいます。
何かの裏技でしょうか?

Aベストアンサー

OS側の時刻表示の設定です。
Windows2000の場合はコントロールパネルの地域のオプションの時刻タブで
時刻の形式「tt hh:mm:ss」
午前の記号「AM」
午後の記号「PM」
にすればそうなります。
ただタスクバーの時計の表示もこの形式になりますが...

QExcelでセルに入っている数字を時刻(0:00:00)として表示する方法(※セルの表示形式の変更ではありません。)

   A   B  
1  24  0:00:24
2  76  0:01:16

セルA1に「24」という数字が入っています。
セルA1の数字を秒として、セルB1に「0:00:24」と表示させたいです。

セルA2、B2のようにもできれば理想です。

単純に関数を使って「="0:00:""A1"」とB2に入力しても、正しく表示はできませんでした。
「="0:00:"A1」も同様に、関数ではないのでエラーです。

方法ご教授お願いいたします。

Aベストアンサー

一例です。
B1に=A1/24/60/60で如何でしょうか。(セルの書式設定で表示形式を時分秒に変更して下さい)


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

人気Q&Aランキング

おすすめ情報