タイムカードから就業時間計算する方法
- Excel2000でタイムカードの出勤・退出時刻を入力して、就業時間を計算する方法を紹介します。
- 定時や休憩時間を考慮して、タイムカードデータを処理して正確な就業時間を算出する手順を説明します。
- 深夜や夜間の勤務時間を含む場合にも対応した計算式を使って、正確な出勤時間と退勤時間を求める方法を解説します。
- ベストアンサー
タイムカードから就業時間計算
Excel2000でタイムカードの出勤・退出時刻を入力して、就業時間を出そうとしています。 定時は8:30～17:15、休憩は12:00～12:50、14:50～15:00、17:15～17:30です。 http://www.h3.dion.ne.jp/~sakatsu/WorkingTimeCalc.htm ↑このサイトを参考に、 A列に出勤時刻(手入力)、 B列に退出時刻(手入力)、 C列に24時補正、 =IF(OR(A2="",B2=""),"",IF(A2<B2,B2,B2+1)) D列に深夜1(0:00～8:30)、 =IF(OR(A2="",C2=""),"",IF(AND(A2<TIMEVALUE("8:30"),C2>=TIMEVALUE("0:00")),MIN(C2,TIMEVALUE("8:30"))-MAX(A2,TIMEVALUE("0:00")),0)) E列に午前(8:30～12:00)、 =IF(OR(A2="",C2=""),"",IF(AND(A2<TIMEVALUE("12:00"),C2>=TIMEVALUE("8:30")),MIN(C2,TIMEVALUE("12:00"))-MAX(A2,TIMEVALUE("8:30")),0)) …の要領で、F列に午後1(12:50～14:50)、G列に午後2(15:00～17:15)、H列に夜(17:30～24:00)の式をそれぞれ入力し、I列でD～H列を合計しています。=SUM(D2:H2) このうち午前・午後1・午後2は正確に表示されるのですが、深夜・夜が0:00のままで表示されません。(退出を夜or深夜にしても0:00のままです) 午前・午後と同じような式を使っているのにどうしてなのでしょうか。どうすれば正確に出るのでしょうか。 セルの書式設定はユーザー定義で[h]:mmにしています。 今日午後から連休明けまではここを覗けないのでお礼ができませんが、よろしくお願いします。
- mok1
- お礼率84% (253/300)
- オフィス系ソフト
- 回答数2
- ありがとう数4
- みんなの回答 （2）
- 専門家の回答
質問者が選んだベストアンサー
D列の深夜ですが式間違えてませんか？ 計算するのは早出(8:30以前の出社）と深夜残業(24:00以降の残業)の計なので IF文は =IF(OR(A2="",C2=""),"",IF(OR(A2<TIMEVALUE("8:30"),C2>=1),TIMEVALUE("8:30")-MIN(A2,TIMEVALUE("8:30")) +MAX(C2,1)-1,0))
その他の回答 (1)
- mshr1962
- ベストアンサー率39% (7418/18948)
#1のmshr1962です。 ＞文字数オーバーで質問には書けませんでした うまく、いかなかった方の式だけ載せればよかったのでは？ 本題ですがtimevalue("24:00")はtimevalue("0:00")と同じ値(0)を返しますので 数字の1に置き換えてください。 後は普通に計算できると思います。
お礼
ありがとうございます！こちらも無事出ました！ なんでこうなるのか全くわからないのが悔しいですが… これから連休明けまでここを覗けないので、取り急ぎお礼まで。 後ほどポイント発行させていただきます。
関連するQ&A
- 時刻の計算結果を15分未満で切り捨てたい、他
勤怠簿を作っています。以前に↓にて質問させていただきました。 http://oshiete1.goo.ne.jp/kotaeru.php3?q=1652568 質問した結果、無事「深夜」「夜」の時間が出るようになりました。 そこで新たに質問が2つあります。 (1)今度はこの「深夜」「夜」の計算結果を15分未満切捨てにしたいのですが、どうすればいいのでしょうか。 「午前」「午後1」「午後2」は15分未満切捨てができています。この式を元に自分でも格闘してみたいのですが、できませんでした。。。 ちなみに「深夜」の式は =IF(OR(B2="",D2=""),"",IF(OR(B2<TIMEVALUE("8:30"),D2>=1),TIMEVALUE("8:30")-MIN(B2,TIMEVALUE("8:30"))+MAX(D2,1)-1,0)) 「夜」は =IF(OR(B2="",D2=""),"",IF(AND(B2<1,D2>=TIMEVALUE("17:30")),MIN(D2,1)-MAX(B2,TIMEVALUE("17:30")),0)) になっています。 (2)出勤が8:15以前の場合に早出としてカウントしないようにするには、どうすればいいでしょうか。 よろしくおねがいします。。。
- ベストアンサー
- オフィス系ソフト
- Excel2003 深夜残業時間の計算
少し長い質問になりますが、よろしくお願いいたします。 出社・退社の時間から、早朝残業や夕方残業、深夜残業など、 残業時間を算出する数式を作成していますがどうにもうまく出来ません。 残業となる時間の切り分けは、平日は以下のようになっています。、 早朝残業→5：00～8：30 基本時間→8：30～17：00 通常残業→17：00～22：00 深夜残業→22：00～翌朝5：00 例えば平日、朝6：00出社→23：00退社であれば、 早朝残業が2.5、通常残業が5.0、深夜残業が1.0となります。 深夜残業を出す数式を、以下のように作成してみたのですが、うまく算出できません。 C列に出社、D列に退社、入力はコロンを挟んだ時間の形式で、表示形式は[h]:mmです。 A列に日付、B列に祝日のフラグ、数式はG列に入れています。 =IF(C10="",0,IF(OR(TEXT(A10,"aaa")="日",TEXT(A10,"aaa")="土",B10="祝"),0,IF(AND(D10>=VALUE("22:00"),C10<=VALUE("5:00")),ROUND((MIN("29:00",D10)-"22:00")*24,2),IF(AND(C10>=VALUE("0:00"),C10<VALUE("5:00")),ROUND((MIN("5:00",D10)-C10)*24,2),0)))) ですが、出社13:00→退社27:00のところで、深夜残業が0.0になってしまい、 数式の IF(AND(D10>=VALUE("22:00"),C10<=VALUE("5:00"))をOR式に変更しました。 =IF(C10="",0,IF(OR(TEXT(A10,"aaa")="日",TEXT(A10,"aaa")="土",B10="祝"),0,IF(OR(D10>=VALUE("22:00"),C10<=VALUE("5:00")),ROUND((MIN("29:00",D10)-"22:00")*24,2),IF(AND(C10>=VALUE("0:00"),C10<VALUE("5:00")),ROUND((MIN("5:00",D10)-C10)*24,2),0)))) すると、出社0：00→退社18：30のところで、深夜残業が0.0になってしまいました。 両方をカバーできる数式はどのようになるのか、色々試しても出来ません。 お分かりになる方、いらっしゃいましたらお知恵を貸してください。 説明がうまく出来ていないかもしれません、分かりづらい文章で申し訳ありませんが どうぞよろしくお願いいたします。
- ベストアンサー
- その他MS Office製品
- エクセルで時間計算
エクセルで時間計算をしているのですが A1セルに開始時刻 B1セルに終了時刻 C1セルは作業時間 9：00 4：00 7：00 このような表を作っています 「C1」には（B1+12：00）マイナス「A1」と数式があるのですが 「B1」に入る時刻が午後でなく午前11：00だと 「C1」の数式のままでは （11：00+12：00）－ 9：00 で 14：00 となってしまいます 「B1」には２４時間形式で午後4：00を16：00と入力すればよいのですが ２４時間形式を使わず9：00マイナス4：00を正しく計算するには どのような数式にすればよいのでしょうか IF文を使うのかなとも思うのですが 見当がつきません。宜しくお願いいたします。
- 締切済み
- オフィス系ソフト
- 【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で条件分岐して入力してみたり、過去の質問も検索してみましたが、思うように表示させることができませんでしたので、質問させていただきました。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- 深夜勤務を含む時間外割増賃金計算シート
24時間稼動の工場で働く人の割増賃金を計算するためのタイムシートを作成中です。次のように進んでいます。尚、深夜残業時間帯は午後10時から翌朝5時までとしています。 Ｅ列：出社時刻 Ｆ列：退社時刻 Ｇ列：就労時間合計（休憩１Ｈのぞく） ＝IF(ISNUMBER(E8),F8-E8-TIME(1,0,0),"") Ｈ列：残業時間（8時間を超えた分、25%割増） ＝IF(OR(E8="",F8=""),"",MAX(MIN(F8+(F8<E8),22/24)-E8-18/48,0)) Ｉ列：深夜労働１ （休日以外、25％割増） ＝IF(OR(E9="",F9=""),"",MAX(F9(F9<E9),22/24)-22/24) Ｊ列：深夜労働２（休日以外で8時間を超えた部分、50%割増） ＊８Ｈを超える残業時間が深夜時間帯にかかる部分 Ｋ列：休日労働１ （35％割増） ＝IF(OR(E9="",F9=""),"",MIN(F9+(F9<E9),22/24)-E9-1/24) Ｌ列：休日労働２ （深夜労働時間帯にかかる部分、60％割増） Ｊ列とＬ列にどのように式を入れたらよいのかわからないのですが、現状だと他の列の式も変える必要がありそうです。というか全部変える必要あり？ 極端な例ですが、普通の日に10時出社、翌朝6時退社した場合、Ｈ列４Ｈ、 Ｉ列７Ｈとなるべきですが、現状だとＨ列３Ｈ，Ｉ列８Ｈと出てしまいます。 Ｊ列、Ｌ列の式を含めて、どのように修整したらよいのか、教えてください。。どうぞ宜しくお願い致します。何だかこれを作るのに疲れてしまって、いいフリーソフトが無いか探してみましたが、見つかりませんでした。。
- 締切済み
- オフィス系ソフト
- 給料計算での時間のずれ
給料計算なんですが、出勤時間‐退出時間、１０分刻みで計算を出しいて、１８時以降から時給が変わるんですけど、なぜか18：20と19：20退出の時だけ10分ずつ１８時までの時給と18時以降の時給の所に振る分けられてしまいます。どうすればよいのでしょうか？18：21や19：21、20：20以降はちゃんと振り分けられます。 Ｄ列（850円時給）に=FLOOR(IF(AF4>=TIMEVALUE("10:00"),0,IF(AG4>TIME(10,0,0),TIME(10,0,0)-AF4,)),"0:10") E列（800円時給）に =FLOOR((AG4-AF4)-AH4-AK4,"0:10") F列（900円時給）に =FLOOR(IF(AG4>TIMEVALUE("18:00"),AG4-TIMEVALUE("18:00"),0),"0:10") 例 9：00～18：20 だと １時間が850円、8時間10分が800円、10分が900円と振分けられてしまいます。本当は900円の所が20分にならなければなりません。 宜しくお願いいたします。
- 締切済み
- アンケート
- 出勤時間表の作成
カレンダー（weekday(date))関数で出勤表を作成しております。 A1B1は「月」、C1D1は「火」、E1F1は「水」と順に入力しています。 A2・C2・E2は午前、B2・D2・F2は午後を入力、A1B1は「１」、C1D1は「２」、E1F1は「３」と入力しています。 A4のセルにA3が空白の場合は空白に、A1セルが月曜日および水曜日でA2セルが午前の場合は、２（時間）を記入、A1セルが火曜日で午前の場合は、５（時間）、B4のセルにB3が空白の場合は空白に、B1セルが月曜日および水曜日でB2セルが午後の場合は、３，５（時間）を記入、B1セルが火曜日で午後の場合は、１（時間）を記入しようと思います。 順次4行をコピーしていこうと思います。 ※A1B1は「月」、C1D1は「火」、E1F1は「水」を別々入力しているのは、A4,B4,C4,D4,E4,F4に、A4セルにIF関数（if(A1="月",・・・・）と入力した場合、A1のセルしか見に行かないので、2つ作成しています。 作成した関数（if(A3="","",if(or(A1,"月","水",A2="午前","2",)if(or(A1,"火","午前","5"),と続けてますが、表示できません。 VBA・関数での回答をお願いします。
- 締切済み
- Visual Basic
- エクセルの時間計算について
エクセルで時間の計算をしたいのですが、うまくできず困っています。 A列には 1315、747、など3～4桁の数値が、B列にも 1532、916など同じく3～4桁の数値が入っています。これはそれぞれ 13:15、7:47、15:32、9:16のように時刻を表しており、A列は活動開始時刻、B列は活動終了時刻となっています。 C列には「TEXT(A2,"0!:00")-TEXT(B2,"0!:00")」という数式を入力し、実質活動時間を計算しています。 このようなデータが約9000行あります。C列の活動時間を合計して、時間数を出したいのですが、どのような計算式を用いればよいでしょうか？SUMでは出ないようですので・・・ ややこしいですが、お知恵をお貸し下さい。よろしくお願いします。
- 締切済み
- Windows XP
- EXCEL2000でこんな時間の計算はできますか？
EXCEL2000で下記のようなデータが入っています。 A列（時刻）B列（標準） 13:00 50 14:00 60 23:30 40 A列とB列を足してC列に時間を表示したいと考えています。 ※上記の例では、 13：50 15：00 0：10 とC列に表示されるようにしたい。 このような計算が可能かどうか？ もし無理ならばどのように元データを入力すべきか、アドバイスをお願いいたします。
- ベストアンサー
- オフィス系ソフト
- Excel：勤務時間の計算がうまくいかない。
No.1468024の質問をした者です。 勤務表を作成しています。（Excel2002を使用） 終了時刻（D列）ー開始時刻（C列）ー休憩時間（E列）=当日の勤務時間を計算しています。 No.1468024の質問でアドバイスをいただき、以下のような計算式を記述しました。 ＜例＞ =AND(C13*D13)*(MIN(IF(AND(D13>="12:00"*1,D13<="13:00"*1),"13:00",D13),"17:40")-MAX(IF(AND(C13>="12:00"*1,C13<="13:00"*1),"12:00",C13),"09:00")-E13+FLOOR(MAX(D13,"18:00")-"17:59:59","0:15")) ほぼうまくいくのですが、開始時刻9:00終了時刻12:00とした場合、勤務時間4:00となり、間違ってしまいます。（正しくは勤務時間3:00。また開始時刻9:00終了時刻が12時から13時までの間の時刻で入力された場合も勤務時間3:00と出力したいです。） 休憩時間セルに以下のような計算式を書いています。 ＜例＞ =IF(COUNT(C13:D13)<2,0,IF(D13-"12:00"*1<=0,0,IF(C13-"12:00"*1>=0,0,IF(D13<"18:00"*1,"1:00","1:20")))) 休憩時間は12時から13時までの1時間。 終了時刻が17:40以降なら20分休憩時間が加算されます。 開始、終了が入力されていないと0:00表示。 言葉足らずでしたら、進んで補足させていただきますので、よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
お礼
ありがとうございます！D列ちゃんと出ました！ =IF(OR(A2="",C2=""),"",IF(AND(A2<TIMEVALUE("終了時刻"),C2>=TIMEVALUE("開始時刻")),MIN(C2,TIMEVALUE("終了時刻"))-MAX(A2,TIMEVALUE("開始時刻")),0)) だと思っていたのですが… ちなみに夜(17:30～24:00)のH列の式は =IF(OR(A2="",C2=""),"",IF(AND(A2<TIMEVALUE("24:00"),C2>=TIMEVALUE("17:30")),MIN(C2,TIMEVALUE("24:00"))-MAX(A2,TIMEVALUE("17:30")),0)) となっているのですが(文字数オーバーで質問には書けませんでした)、 これはどこが間違っているのでしょうか？