日報の時間計算を行う関数について

質問

勤務時間を管理の日報を作成しています。以下の条件をつけて出勤時間と退社時間を記入するだけで1日の総勤務時間を表示してくれる関数をご教示お願いします。

・9:00出勤で18:00定時退社で15分単位で表示
・セル（開始）は9:00、（終了）に18:00以降の時間を手入力
・セル（工数）に関数で総勤務時間を計算
・9:00から18:00までに1時間15分の休憩をとるため、自動的に差し引いて計算
・残業は18:15からカウントされるため、18:30以降残業して退社した場合は15分差し引いて計算
・残業最終時間0:00までとして計算
・セル（開始）、（終了）に9:00～0:00の15分単位の時間以外を入力はない前提とします
・遅刻、早退による変則時間の入力はない前提とします

【例】
（開始）9:00、（終了）18:00の時は（工数）に7.75
（開始）9:00、（終了）18:15の時は（工数）に7.75
（開始）9:00、（終了）18:30の時は（工数）に8
（開始）9:00、（終了）0:00の時は（工数）に13.5

よろしくお願いします。

回答No.2

mt2008 · ベストアンサー

A1が開始、B1が終了の場合、こんな感じでいかがでしょうか。
=INT((B1-A1)*24*4)/4+(A1>B1)*23.75-1.25-0.25*(B1>=TIMEVALUE("18:15"))

15分単位の切捨てには通常はFLOOR関数を使用しますが、日を跨いだ時の処理まで考えると式が長くなりそうだったと個人的好みで使用しませんでした。

お礼

ありがとうございます。活用させてもらいます。

回答No.4

nattocurry

開始は必ず9:00のようですので、終了がB1だとしたら、
=IF(B1=VALUE("0:00"),13.5,IF(B1=VALUE("18:00"),7.75,7.75+(B1-VALUE("18:15"))*24))
でも良いですね。

もしB1が0:00だったら13.5、
そうじゃなかったら
　　もしB1が18:00だったら7.75、
　　そうじゃなかったらB1から18:15を引いて、それを7.75に足す

という式です。

24を掛けているのは、Excelでは1日を1として扱っています。
1時間は1/24です。
1時間を1として扱うために、24を掛けています。

お礼

ありがとうございます。うまく表示できました。

回答No.3

layy

この手は、開始と終了で工数は確定しますから、計算しなくてもＩＦ文列挙でも２次元配列でもできます。結果を急ぐならそれでも可能です。

四則演算するには、基本的には時分単位をやめると解決。１時間１５分を７５分にする。表現戻す時は６０分で割る、余りを出す。

時間計算に困ったのか、パターンをスッキリしたいのか、どうなんでしょう。

事例多数の要件ですからサイト探したても早い気がします。

エクセルで用意された関数はないのでは？。自分でつくることになります。

お礼

ありがとうございます。回答いただいた関数を使用します。

回答No.1

mu2011

一例です。
仮にA1に開始時間、B1に０：００は２４：００と入力する事としています。
　=IF(COUNT(A1:B1)=2,IF(B1>"18:00"*1,(B1-A1-"1:30")*24,(B1-A1-"1:15")*24),"")

因みにここまで条件設定出来ているのだから丸投げせず、ご質問者の不明点を記載した方が良いと思います。

お礼

丸投げながらも回答ありがとうございます。うまくいきました。