異なる書式を持つ勤怠フォーマットを作っています。（ＩＦ関数）

質問

皆様、いつもお世話になっております。
勤怠のフォーマット作りをしています。何時間も数式をいじっておりますが、皆目分かりません…。
良い方法がありましたら、プロの方、宜しくお願い致します。いつもありがとうございます。

・M列：実働時間【ユーザー定義：h:mm】
・O列：実働時間【ユーザー定義：Ｇ／標準】…Ｍ列を参照し、この時間を合計して、労働時間を計算。
・Q列：残業時間【ユーザー定義：Ｇ／標準】

【やりたいこと】
１．8時間を下回る時には、“時間＆分”で表したい。（O列）（（例）6.5という風に）
２．8時間以上の時には別のセルに残業時間を表し（Q列）、8としたい。（O列）
３．残業時間を0.5、1.0といった数値で表し、合計して計算したい。（Q列）

O2に、
If（M2=””,””,if（HOUR（M2<8,HOUR（M2）+MINUTE（M2）/60,HOUR（M2）-Q2）

としたのですが、

【エラー】
１．残業（Q列）を空欄にすると、＃VALUEが出てしまう。
２．残業（Q列）に数値を入力　●0.5【Ｇ／標準】→実働時間（O列）　7.5、●1【Ｇ／標準】→実働時間（O列）　8.0　となってしまう。

以上です。分かりにくい場合は補足致します。

回答No.2

135ok · ベストアンサー

よく理解していませんが、なんとなく作ってみました。
添付資料を参照ください。

Ｏ列は項目（営動時間？）も、赤い部分も読めないので憶測で記入しています。
Ｆ２　=IF(C4="","",D4-C4)
　　　*出社時間が空欄の時は空欄、以外は退社時間－出社時間
Ｇ２　=IF(F4="","",F4-E4)
　　　*就労時間が空欄の時は空欄、以外は就労時間－休憩時間
Ｈ２　=IF($G4="","",IF($G4>TIME(8,0,0),VLOOKUP($G4-TIME(8,0,0),$K$3:$M$7,COLUMN(A1)+1,1),""))
　　　*営業時間が空欄の時は空欄。営業時間が8時間を越える時は、営業時間－８時間で残業時間を求め、残業リストの表から値を表示、以外は空欄。
　　　*Ｈ列、Ｉ列の表示させたい範囲にコピー

大はずれの気がしてなりませんが・・・

回答No.3

135ok

No.2です。
残業のリストに誤りがありました。下記の通り変更ください。

残業リスト　残業　時間
0:30　残業30分　0.5

０→０：３０

お礼

【ご回答を下さったお二人へ】

お礼が遅くなり申し訳ありません。ご回答嬉しかったです。

さて、結論から申し上げますと、次の数式を入力し、OKとなりました。
●Ｑ列に、If（M2=””,””,if（HOUR（M2）+MINUTE（M2）<8,HOUR（M2）+MINUTE（M2）/60,HOUR（M2）-Q2）
●U7に"なし"、V7に"0"を入力し（pf.黄色の残業リスト）、P7・Q7にVLOOK関数で"残業時間なし"の場合、"0"が出るようにする。（pf.ピンクの列）
●残業リストの合計欄（pf.ピンクの列）：=COUNTIF（P7:P37,"残業*"）

【ポイントと理由】
●20P…画像を表して、熱心に考えて下さり、、新しい関数も教えて下さった135ok様に。
●10P…答えにくい質問に素早く回答をくださり、私の弱点を指摘して下さったcoaltar様に。

【最後に】
　今回、最初に3時間程一生懸命考えて答えが出なかったのですが、休憩を入れて再び考えた所、ひらめきました。お二人の助力には本当に感謝しております。また、機会がありましたら、どうか力になってください。本当にありがとうございました。<ciaoyeti>

回答No.1

某HN クロメート(Chromate)（@CoalTar）

回答が付かないようなので。
まず、添付図の文字はつぶれてしまっていて、書いてあることがわかりません。努力すれば読めるのかもしれませんが・・・。

そして、サンプルを提示するようにしてください。

質問を見ていて、シリアル値が理解できてないと感じました
="24:00"*1 の答えは1です。1日は1です。
つまり、="8:00"*24 の答えは8となります。

＞プロの方、宜しくお願い致します。
Excelにおいて、VBA以外はほとんど「専門家」で回答してますが、
いつも、「微妙」だと思って回答しています。(だから、参考意見ばっかり)
Excelの知識はあっても、質問の内容に対しての専門知識が無いことは多いですから。
でも、そうすると専門家はほとんどいないんじゃないかって思いますし・・・。葛藤