勤怠管理表をエクセルで作成しています。
一日の労働時間の計算式について教えてください。

退社時間(17:30)−出社時間(8:30)−休憩1時間(12時から13時)で計算するとして、
退社が12時〜13時の間だったときは、労働時間3時間半の答えにしたいです。
14時退社のときは、4時間半にしたいです。
簡単な関数にするとしたら、どんなふうにしたらよいでしょうか?

このQ&Aに関連する最新のQ&A

A 回答 (4件)

こんばんは!



午後の休憩時間は考慮しなくて良いのですね?

↓の画像でC2セルに
=IF(B2>"13:00"*1,B2-A2-"1:00",MIN("12:00",B2)-A2)

という数式を入れフィルハンドルで下へコピーしています。
※ エラー処理はしていません。m(_ _)m
「エクセル 時間計算について」の回答画像3
    • good
    • 1
この回答へのお礼

ありがとうございました。
MIN関数を使ってシンプルな数式になりました。
反対にMAX関数で早退の計算も簡単にできました。

お礼日時:2017/03/21 14:47

A1:出社時刻


B1:退社時刻
C1:休憩開始時刻
D1:休憩終了時刻
とします。
(C1,D1は直接入力でも良かったのですが、変更可能な様に参照としました)

=B1-A1-IF(A1<C1,IF(B1>D1,D1-C1,IF(B1>C1,B1-C1,0)),IF(A1<D1,IF(B1>D1,D1-A1,B1-A1),0))
B1-A1で出社時刻から退社時刻までの時間を計算しています。
そこから休憩時間にあたる部分を引いています。

休憩時間にあたる部分は、IFを用いて、
出社が休憩開始前で、退社が休憩終了後なら、休憩開始~終了
出社が休憩開始前で、退社が休憩時間中なら、休憩開始~退社
出社が休憩開始前で、退社が休憩開始前なら、0
出社が休憩時間中で、退社が休憩終了後なら、出社~休憩終了
出社が休憩時間中で、退社も休憩時間中なら、出社~退社
出社が休憩終了後なら、0
を休憩時間として計算しています。
    • good
    • 0
この回答へのお礼

ありがとうございました。
丁寧な解説でわかりやすかったです。

お礼日時:2017/03/21 14:46

下図のようなレイアウトだった場合C2セルに以下の式を入力後、下にコピー


「=IF(OR(A2="",B2=""),"",IF(B2<=TIMEVALUE("12:00"),B2-A2,IF(B2<=TIMEVALUE("13:00"),TIMEVALUE("12:00")-A2,B2-A2-TIMEVALUE("1:00"))))」
「エクセル 時間計算について」の回答画像2
    • good
    • 0
この回答へのお礼

ありがとうございました。

お礼日時:2017/03/21 14:48

単純にこんな感じでどうでしょうか。


C2に以下式を入力

=IF(AND(B2>=TIME(12,0,0),B2<=TIME(13,0,0)),TIME(3,30,0),B2-A2-TIME(1,0,0))
「エクセル 時間計算について」の回答画像1
    • good
    • 1
この回答へのお礼

ありがとうございました。

お礼日時:2017/03/21 14:47

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

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

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

Q延滞金計算のユーザー定義関数で迷ってます。納期限が11/30~12/30の時端数処理でズレでます。

はじめまして、yasshi42と申します。ここを利用するのは初めてです。よろしくお願いします。
最初はVLOOKUP関数を使っていたのですが、各年毎に計算して端数切捨てしていたものを、利率が同じ間は日数を加算して利率が変わったと時に計算するように改修したいのですが未熟なのでうまくいきません。納期限が11/30~12/30はうまくいっているのですが…
↓を試したのですがだめでした。
if t3.Offset(0, 5)=t3.Offset(1, 5) Then  'もし一つしたの利率がおなじなら
Days= Days+ t3.Offset(1, 3) '日数を加算する。
Else
(略)
End if
↓は2年目以降のLoop処理です。
Do Until iNen = eNen
 Set t3 = Range("利率設定").Find(What:=iNen)
 kaisibi = t3.Offset(0, 1)
 endbi = t3.Offset(0, 2)
 Days = t3.Offset(0, 3)
 rituY = t3.Offset(0, 5)
(ここに上記のIf文入れましたがだめでした。)
  If endbi < keisanbi Then
  Yentai2nd = Entai * rituY * Days / 365
  YEntai = YEntai + Yentai2nd
  End If
  iNen = iNen + 1
  Set t3 = Nothing
Loop

文字数制限があるみたいなのでコードの全部はのせてません。↓に載せてます。
http://arakurikaesiki.blog.fc2.com/blog-entry-69.htm

はじめまして、yasshi42と申します。ここを利用するのは初めてです。よろしくお願いします。
最初はVLOOKUP関数を使っていたのですが、各年毎に計算して端数切捨てしていたものを、利率が同じ間は日数を加算して利率が変わったと時に計算するように改修したいのですが未熟なのでうまくいきません。納期限が11/30~12/30はうまくいっているのですが…
↓を試したのですがだめでした。
if t3.Offset(0, 5)=t3.Offset(1, 5) Then  'もし一つしたの利率がおなじなら
Days= Days+ t3.Offset(1, 3) '日数を加算する。...続きを読む

Aベストアンサー

こんにちは。

全部見切れてはいないのですが、こういう金利計算の基本的な扱いを間違えていると思います。もちろん、VBAを使う使わないに関わらず、気をつけなくてはならない問題があります。

一言「パソコンは、2進で動いています。したがって、小数点以下の数字には、丸め誤差が出ます。」ということです。

しかし、逆にVBAで始めてきちんとした修正ができる問題です。

コードをみると、Double型の変数があちこちにありますが、それはありないことです。2進で計算させたら、割り切れる数以外は、四則演算でほぼ狂います。もちろん、そのすべてが小数点以下を取り扱うこととは言えませんが。

Dim Yentai1st, rituY As Double '←X
Dim endbi, kaisibi As Date

それと、VBAは、VB6のDNAを継ぐものですから、VB.Net とは違い、そのような書き方をすると、Yentai1st は、Variant になっています。

Double型の代わりに、Currency 型(または、Decimal型/CDec で変換する)を使うというのが、決まりになっています。つまり、10進で計算させることです。電卓などでは、そうした完全な補正措置が取られていますが、Excelの表面上は、ある程度の補正はしているそうですが、VBAにおいては、補正も保護もなく、数字が丸裸にされてしまいます。

だから、Excelの表計算自体も、信用できません。例えば、小数点をドラッグコピーした場合に、万が一にも、表に隠れた小さな数値が混じっていれば、それだけで、実際にズレが生じてしまいますので、小数点の扱いは、ドラッグコピーなどは極めて気をつけなくてはなりませし、ワークシートの関数の二次計算も、そのままだし概ね不可だと思って間違いありません。一旦、Fixed/VBAでは、Fix 関数を使うなりしないと、正確には出てきません。マイクロソフト側では、Round関数を勧めています。なお、今回は使われていませんが、INT関数は、間違いの元ですから、気をつけたほうがよいです。

それ以外の問題で、問題箇所を探すというと、全部解体工事になってしまうと思います。

こんにちは。

全部見切れてはいないのですが、こういう金利計算の基本的な扱いを間違えていると思います。もちろん、VBAを使う使わないに関わらず、気をつけなくてはならない問題があります。

一言「パソコンは、2進で動いています。したがって、小数点以下の数字には、丸め誤差が出ます。」ということです。

しかし、逆にVBAで始めてきちんとした修正ができる問題です。

コードをみると、Double型の変数があちこちにありますが、それはありないことです。2進で計算させたら、割り切れる数以外は、四則演算で...続きを読む

Q(続)エクセルでVBAを使い、時間入力10:30:45(10時30分45秒)の:コロン省略したい。

始業と終業の時間を入力し勤務時間を計算しております。
時間は時:分:秒まで入力します。
例えば始業時間はA1に8:30:25 、終業時間はB1に17:00:45と入れております。
コロン(:)を省略できるととても楽になります。8:30:25は83025と入力、17:00:45は170045と入力して、
表示にはコロンが入り、セルの書式としては時刻と認識し時間差を(時間:分:秒)と計算出来ればありがたいです。
よろしくお願い致します。

VBAを使った(時間:分)のコロンを入力しないで、かつ表示にはコロンが入り、セルの書式としては時刻と認識し時間計算が出来る例は検索できました。
下記です。 t****さんありがとうございました。
ですがそれを秒まで拡大し(時間:分:秒)に対応できるVBAに変更できず困っております。
何卒よろしくお願い致します。

-----------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
If Intersect(Target, Range("A:B")) Is Nothing Or Target.Count > 1 Then Exit Sub
With Target
If .Value <> "" Then
If IsNumeric(.Value) Then
If .Value < 2400 And .Value Mod 100 < 60 Then
Application.EnableEvents = False
.Value = TimeSerial(Int(.Value / 100), .Value Mod 100, 0)
.NumberFormatLocal = "h:mm"
Application.EnableEvents = True
Else
MsgBox "入力値が不正です"
.Select
.ClearContents
End If
End If
End If
End With
End Sub 'この行まで
-----------------------------------------------------------------------------

始業と終業の時間を入力し勤務時間を計算しております。
時間は時:分:秒まで入力します。
例えば始業時間はA1に8:30:25 、終業時間はB1に17:00:45と入れております。
コロン(:)を省略できるととても楽になります。8:30:25は83025と入力、17:00:45は170045と入力して、
表示にはコロンが入り、セルの書式としては時刻と認識し時間差を(時間:分:秒)と計算出来ればありがたいです。
よろしくお願い致します。

VBAを使った(時間:分)のコロンを入力しないで、かつ表示にはコロンが入り、セ...続きを読む

Aベストアンサー

変換ロジックを関数化して実装してみました。
「8:30:25」「83025」どちらの入力でも対応できているはずです。
ちなみに、2未満を入力すると日とみなし、2以上を入力すると秒とみなして表示されます(この辺はやってみて下さい)。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
If Intersect(Target, Range("A:B")) Is Nothing Or Target.Count > 1 Then Exit Sub
With Target
If .Value <> "" Then
Application.EnableEvents = False
.Value = MyTimeValue(.Value)
If .Value = "" Then
MsgBox "入力値が不正です"
.Select
Else
.NumberFormatLocal = "[h]:mm:ss"
End If
Application.EnableEvents = True
End If
End With
End Sub 'この行まで

Function MyTimeValue(MyTime As Variant) As Variant
Dim t As Variant
Dim d As Variant
If IsNumeric(MyTime) And MyTime < 2 Then
MyTimeValue = MyTime
Exit Function
End If
On Error GoTo Fin
t = Split(Format(MyTime, "000:00:00"), ":")
d = Int(t(0) / 24)
t(0) = t(0) Mod 24
MyTimeValue = d + TimeSerial(t(0), t(1), t(2))
Fin:
End Function

変換ロジックを関数化して実装してみました。
「8:30:25」「83025」どちらの入力でも対応できているはずです。
ちなみに、2未満を入力すると日とみなし、2以上を入力すると秒とみなして表示されます(この辺はやってみて下さい)。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
If Intersect(Target, Range("A:B")) Is Nothing Or Target.Count > 1 Then Exit Sub
With Target
If .Value <> "" Then
Application.EnableEvents = False
.Value = ...続きを読む

Qsheet1(1月)からsheet12(12月)をsheet13(H17年度)に集計したい

sheet1(1月)
A   B  C
1 得意先  銀行  金額
2 aa社 xx 50
3 cc店 yy 30
4 ee社 xx 20

sheet2(2月)
  A B C
1 bb zz 50
2 ee xx 30
3 cc yy 50
4 dd ww 30

目的のsheet
sheet13(H17年度)
A     B    C    D
1 得意先  1月   2月   3月
2 aa社   50  
3 bb社 50
4 cc社 30 50
5 dd社 30
6 ee社 20 30

Aベストアンサー

元ファイルをシート分けせずに、月フィールドを作って整理し
ピポットテーブルを使えば一発です。

得意先銀行金額月
aaxx501月
ccyy301月
eexx201月
bbzz502月
eexx302月
ccyy502月
ddww302月

としておき、ピポットテーブルで
行:得意先、列:月、データ:合計/金額とすればよいでしょう

Qエクセルで残業時間を時間帯によって+1時間+30分にしたい

関数がわからなくて困っています。
基本は8:30~17:30
残業(早出含む)とされる時間帯
 5:00~7:30/17:30~19:30 この時間帯は1.0時間刻み
 (退社が18:00だと残業は0)
 7:30~8:30/19:30~24:00 この時間帯は0.5時間刻み
 (退社が20:00だと残業は2.5)
休憩は1:00(12:00~13:00)
以上の条件でC列―出社時刻、D列―退社時刻、
      E列―勤務時間、F列―残業時間
で表を作った場合、F列の残業時間を出す式がわかりません。
(E列の勤務時間もですね。自分で考えたのはアヤシイ)
E/F列は、例えば1時間30分なら1.5と出てくるようにします。
7:30出社で20:00退社の場合、勤務時間は8.0、残業時間は3.5となる
ようにしたいのです。
時間の計算が苦手で・・・。どなたか教えてください。

Aベストアンサー

No.5です。すいません、条件のはっきりしない部分もありましたので
十分な検証をしていませんでした。
(やはり、17:30より早い時間を入力するケースなどがあるのですね。)
どちらにしましても一部式もおかしかったので、F2の式は下記に訂正を
致します。
E2 =(MIN("17:30",D4)-MAX(C4,"8:30")
  -IF(C4-"13:00"<0,"13:00"-MAX(C4,"12:00"),0))*24
一行につなげて入力してください。
1行目部分はトータル勤務時間、2行目が昼休み分の引き算です。
条件がありませんので、勤務時間は1分単位で計算しています。

F2 =(IF("7:30"-C3>0,FLOOR("7:30"-MAX(C3,"5:00"),"1:00"),0)
+IF("8:30"-C3>0,FLOOR("8:30"-MAX(C3,"7:30"),"0:30"),0)
+IF(D3-"17:30">0,FLOOR(MIN(D3,"19:30")-"17:30","1:00"),0)
+IF(D3-"19:30">0,FLOOR(MIN(D3,"24:00")-"19:30","0:30"),0))*24
一行につなげて入力してください。
各行は順に、5:00~7:30、7:30~8:30、17:30~19:30、19:30~24:00の
残業時間に対応していますので、作業列として分けた方が
分かりやすいかもしれません。

No.5です。すいません、条件のはっきりしない部分もありましたので
十分な検証をしていませんでした。
(やはり、17:30より早い時間を入力するケースなどがあるのですね。)
どちらにしましても一部式もおかしかったので、F2の式は下記に訂正を
致します。
E2 =(MIN("17:30",D4)-MAX(C4,"8:30")
  -IF(C4-"13:00"<0,"13:00"-MAX(C4,"12:00"),0))*24
一行につなげて入力してください。
1行目部分はトータル勤務時間、2行目が昼休み分の引き算です。
条件がありませんので、勤務時間は1分単位で計算してい...続きを読む

QExcelで05-09-26 13:27を2005年9月26日13時27分と認識させたい

A2セルに「05-09-26 13:27」、A3セルに「05-09-30 9:40」、A4セルに「05-10-01 15:35」の様に日時を表す文字列が入ってずーっと下のセルまで続いています。
今の状態だと単なる文字としてしか認識されませんが、これをExcelが日時として認識できる(セルの書式設定を数値にすればシリアル値で表示される状態)ようにするには、どうすれば良いでしょうか?

Aベストアンサー

こんにちは~

次のようにしてみてください。

・A列をすべて選択
・メニューの「データ」-「区切り位置」
・下の<次へ>を 2回クリックして 3画面目へ
・3画面目の「列のデータ形式」を<日付>にして「完了」で、日付データに変換されます。


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

人気Q&Aランキング

おすすめ情報