Excelの計算式で頭を悩ませております。

Question

Excelの計算式で頭を悩ませております。今年の4月から残業時間60H/月 超えになると、残業の単価が1.25→1.5になることをうけ、現在使用しているExcelのデータを少し書き換える作業に取り組みました。

月の総残業時間から60時間を引いて、60時間超分の残業時間に0.25の単価を追加しようとしていますが、どうしても 60時間が引けずに困っています。
全て独学ですが計算式を入れたフォーマットにしております。

ex)
時間外勤務 80ｈ として、Excelの計算式は、こちらに1.25を掛けて残業代を計算しています。いまの書式を出来るだけ変更したくはないので、列だけをズラして、簡易的に80ｈから60ｈを引いて残った20ｈ分に0.25を掛けたのを加算すれば済む。と思っているのですが、そもそも、この考え方が違うのでしょうか？添付写真の黄色マーカーしているところが増やした部分です。

60ｈを単純に引けば良いとは思っていません。時間の計算式やセル設定が必要なのだと思いますが、どのような計算式を入れて設定して良いのかわかりません。
60ｈを別セルに入力すると 1900/2/29 0:00:00 ←このようになってしまい、ユーザー定義で[ｈ]:mmに直してもダメです。

拙い説明ですみませんが、解決の方法はありますか？
宜しくお願いします。

Answer 1

質問等の内容から、数式は
AJ16セルに=AN16*($AJ$12*0.25)
AN16セルに=MAX(0,SUM(AN14:AN15)-60)
でどうでしょうか。

質問とは別ですが、時間外の計算式が間違っていないでしょうか。
P8セルに=IF(M8-$AA$3<0,0,M8-$AA$3)

また、④はトータル時間数を求めているようなのですが、右側の欄に残業時間に②×1.25、③×0.25と係数をかけているような記載があるのが気にかかります。

Answer 2

画像からわかる情報が少ないので、的外れな回答だったら申し訳ありません。
それぞれのセルに入っている関数や、セルの表示形式がわかると回答しやすいです！

方法は2パターンだと思います。
どちらにも共通するのは、"数値の形式を揃える"ということです。
質問者様の例をもとに解説させていただきます。

■パターン1 すべて整数値として計算する
---------------------------------------
A B
1 80.0 時間外
2 0.00 深夜
3 [ ] 時間外60h超
---------------------------------------
画像を拝見する限り、A1、A2のセルには
時間ではなく整数値としての「0.00」が入っているように見えます。
なので、[ ]の表示形式も整数にしてあげれば良いのです。

①A3のセルに、以下の関数を入れてください。
=A1-60

②A3のセルの書式設定を「標準」または「数値」に設定してください。

■パターン2 すべて時刻として計算する
---------------------------------------
A B
1 80:00 時間外
2 0:00 深夜
3 [ ] 時間外60h超
---------------------------------------

①まず、A1、A2のセルの表示形式を「時刻」にしてください。
→わからなかったら返信ください！

②A3のセルに、以下の関数を入れてください。
=A1-60*TIME(1,0,0)

③A3のセルの書式設定を「時刻(hh:mm)」に設定してください。

★左側のタイムカード部分からうまく情報を引っ張るなら、パターン2がおすすめです。

Answer 3

> 1900/2/29 0:00:00

これについては、このセル、もしくは見にいているセルに 60 という整数値がはいっており、表示形式が日付時刻を選択しているからです。

一連の計算流れを、60という整数で処理したいのか、60:00 というシリアル値で処理したいのかで説明が異なります。

簡単に説明すると、

=max(0,ひかれる数-ひく数）

とすれば、60時間超える時間数を算出できます。超えてなければ０出力。ひかれる数とひく数は、シリアル値か整数かどちらかに統一。