[[20230803135032]]　『時間の計算でエラーが出る件』（お米）　ページの最後に飛ぶ

『時間の計算でエラーが出る件』（お米）

先日は法定休日の件でお世話になりました。
またうまくいかない箇所がありましたのでご教授ください。

出退勤の時間から遅刻早退時間を算出する表を作っていますが時刻計算の誤差でエラーが出てしまいます。

遅刻早退時間を有給休暇にすることが可能な為以下の表を作成しました。
セルの書式設定、表示形式はh:mmにしてあります。

　　｜　Ａ列　｜　Ｂ列　｜　Ｃ列　｜　Ｄ列　｜　Ｅ列　　｜　Ｆ列　｜　Ｇ列
――――――――――――――――――――――――――――――――――――――――――――
１　｜シフト　｜　出勤　｜　退勤　｜　休憩　｜勤務時間　｜有給時間｜遅刻早退
――――――――――――――――――――――――――――――――――――――――――――
２　｜　早　　｜　式１　｜　式１　｜　式１　｜　式２　　｜　　　　｜　式３
――――――――――――――――――――――――――――――――――――――――――――　
３　｜　　　　
――――――――――――――――――――――――――――――――――――――――――――
４　｜
――――――――――――――――――――――――――――――――――――――――――――
５　｜所定勤務時間表
――――――――――――――――――――――――――――――――――――――――――――
６　｜シフト　｜　出勤　｜　退勤　｜　休憩　｜勤務時間
――――――――――――――――――――――――――――――――――――――――――――
７　｜　早　　｜７：00　｜16：00　｜１：00　｜=C7-B7-D7　
――――――――――――――――――――――――――――――――――――――――――――
８　｜　遅　　｜13：00　｜22：00　｜１：00　｜=C8-B8-D8
――――――――――――――――――――――――――――――――――――――――――――
９　｜　休　　｜　　　　｜　　　　｜　　　　｜　　　　　
――――――――――――――――――――――――――――――――――――――――――――

式1　下表の所定勤務時間表からA列のシフトに応じて自動で時間が出るように以下の式（列番号のみ変更）を入れています。

=IF(OR(A2=A9,A2=""),"",VLOOKUP(A2,A6:E9,列番号,FALSE))

式2　出退勤と休憩時間から勤務時間を算出します。

=IF(OR(A2=A9,A2=""),"",C2-B2-D2)

式3　所定勤務時間と実際の勤務時間、有給時間を考慮して遅刻早退の時間を算出します。
=IF(OR(A2=A9,A2=""),"",VLOOKUP(A2,A6:E9,5,FALSE)-E2-F2)

遅刻早退や時間の変更があった場合は式を潰して直接入力するのですが、遅刻早退が0：00になるところが######と負のエラーが出てしまいます。

例1…2行目
早番から出退勤を1時間遅くずらして8時間勤務した。

例2…3行目
1時間遅刻したので7時間勤務して1時間有給を使用した。

例3…4行目
休憩を2時間取ったので7時間勤務して1時間有給を使用した。

※式が入っているセルを（）で表記しています

　　｜　Ａ列　｜　Ｂ列　｜　Ｃ列　｜　Ｄ列　｜　Ｅ列　｜　Ｆ列　｜　Ｇ列
――――――――――――――――――――――――――――――――――――――――――――
１　｜シフト　｜　出勤　｜　退勤　｜　休憩　｜勤務時間｜有給時間｜　遅刻早退
――――――――――――――――――――――――――――――――――――――――――――
２　｜　早　　｜８：00　｜17：00　｜(1：00）｜(8：00）｜　　　　｜（#####）例1
――――――――――――――――――――――――――――――――――――――――――――　
３　｜　早　　｜８：00　｜(16：00)｜(1：00）｜(7：00）｜１：00　｜（#####）例2　　　
――――――――――――――――――――――――――――――――――――――――――――
４　｜　早　　｜(7：00）｜(16：00)｜２：00　｜(7：00）｜１：00　｜（#####）例3
――――――――――――――――――――――――――――――――――――――――――――
５　｜所定勤務時間表
――――――――――――――――――――――――――――――――――――――――――――
６　｜シフト　｜　出勤　｜　退勤　｜　休憩　｜勤務時間｜
――――――――――――――――――――――――――――――――――――――――――――
７　｜　早　　｜７：00　｜16：00　｜１：00　｜=C7-B7-D7｜　
――――――――――――――――――――――――――――――――――――――――――――
８　｜　遅　　｜13：00　｜22：00　｜１：00　｜=C8-B8-D8｜　
――――――――――――――――――――――――――――――――――――――――――――
９　｜　休　　｜　　　　｜　　　　｜　　　　｜　　　　｜　
――――――――――――――――――――――――――――――――――――――――――――

いずれも遅刻早退は無いので0：00と出てほしいのにエラーになります。
例2 例3 についてはIF関数を外すか所定勤務時間表の勤務時間を数式ではなく直接入力に変えるとエラーを回避出来ましたがどちらもしたくありません。

例1については上記対応もできませんでした。

例1 例2 例3 どれもオプションの「1904年から計算する」にチェックを入れるとエラーではなく-0：00となりますが集計するした際に端数でズレが起こりそうなので0：00にしたいです。

VALUE(TEXT(時間計算式,"h:mm"))も試しましたが、式?BでVLOOKUPと合わせると#VALUE!のエラーが出てしまいました。

ご助言頂ければ幸いです。
よろしくお願いいたします。

＜使用 Excel：Excel2019、使用 OS：Windows10 ＞

 ＞式3　所定勤務時間と実際の勤務時間、有給時間を考慮して遅刻早退の時間を算出します。
 ＞=IF(OR(A2=A9,A2=""),"",VLOOKUP(A2,A6:E9,5,FALSE)-E2-F2)

 最小単位が「分」なので、一旦1440倍(←24*60）し、Round関数で小数演算誤差を解消する。
 その後1440で割り戻す。

 つまり、→　G2セル =IF(OR(A2=A9,A2=""),"",ROUND((VLOOKUP(A2,A6:E9,5,FALSE)-E2-F2)*1440,0)/1440)

（半平太） 2023/08/03(木) 17:18:47

 その VLOOKUP は、A2が「早」「遅」どちらの場合も結果は「8:00」でしょ？
 VLOOKUP を使う必要ありますか？

 G2 =IF(OR(A2="早",A2="遅"),MAX(0,"8:00"-E2-F2),"")
 これでどうなりますか？
（笑） 2023/08/03(木) 18:04:45

半平太様
出来ました！roundをそこで使うんですね、有難うございました！

笑様
シフトは割愛していますが全10種類以上あり、パートは時間も様々ですのでvlookupが必要でした。
分かりやすいように早番遅番にしたんですが勤務時間は変更するべきでした、申し訳ございません。
有難うございました！
（お米） 2023/08/04(金) 08:48:40

コメント返信：

[ 一覧(最新更新順) ]

[[20230803135032]] 『時間の計算でエラーが出る件』（お米） ページの最後に飛ぶ

[[20230803135032]]　『時間の計算でエラーが出る件』（お米）　ページの最後に飛ぶ