エクセルで給料計算-切捨てと切り上げ

質問

エクセルで給料計算表をいろいろ教えてもらいながら作ったのですが、当初は出社時間も退社時間も15分単位で45～00分は切り上げで00～15分後は切捨てにということでやってみたところ、途中で46～00分までが00分となり00～14分までが00分になるように変更したいのです。前回教えていただいたのでは=round(A1*48, 0)/48をセルに入力するということでしたが、これは45分は00分になってしまいます。何とかお知恵をお貸しください。お願いします。

回答No.5

noname#204879 · ベストアンサー

=IF(MINUTE(A1)<15,TIME(HOUR(A1),0,0),IF(MINUTE(A1)>=46,TIME(HOUR(A1)+1,0,0),TIME(HOUR(A1),30,0)))

お礼

すごーい！これなら分析ツールにチェックを入れなくても大丈夫ですね。ありがとうございます。

回答No.6

koko88okok

前回 ANo.3 です。

かなりややこしい計算になって頭が追いつけません。そこで、考え方だけですが、

通常の勤務時間計算では、出社時間を切上げ(CELING関数)、退社時間を切捨て(FLOOR関数)で計算しますが、

貴社の勤務時間の計算では、出勤時間を15分単位の切捨て(FLOOR関数)、退社時間を15分単位の切上げ(CELING関数)で計算すればご希望に近い時間計算ができるのではないでしょうか？

1ヶ月間の集計で、基準勤務時間を超えた時間を30分単位で切上げた時間数を残業時間とすればよろしいのではないかと思います。

できれば、00分±7.5分、15分±7.5分、・・・のように丸める計算方法があったような気がしますが、思いつきません。

お礼

前回に続き回答ありがとうございます。社長の考えも二転三転してまして、社員のためにと思ってすることが返ってややこしくなっているみたいです。でもおかげてエクセル勉強になりました。これ以上考えが変わらないといいのですが、そうなったらまたお世話になります。

回答No.4

zap35

＃０１です。私も混乱してきました(^^;　補足していただいた例で
＞16:45　　　　→　16:30
＞16:46　　　　→　17:00
＞17:14　　　　→　17:00
＞17:15　　　　→　17:30
16:45は16:30に切り捨て、17:15は17:30に切り上げになっています。

私は一瞬
16:16～16:45　　→　16:30
16:46～17:15　　→　17:00
17:16～17:45　　→　17:30
17:46～18:15　　→　18:00
18:16～18:45　　→　18:30
という規則性があり
　=MROUND(A1-"00:01","00:30")
でいけるかとも思ったのですが、この式では17:15は17:00に切り捨てられてしまいます。

もしかしたら
16:46～17:14　　→　17:00　（この間隔は29分間）
17:15～17:45　　→　17:30　（31分間）
17:46～18:14　　→　18:00　（29分間）
18:15～18:45　　→　18:30　（31分間）
のように不等間隔で切り捨て、切り上げするのでしょうか。
もしそうなら毎時15分の時だけ切り上げするようにすればよいですから以下の式になります
　=MROUND(A1-"00:01"+(MINUTE(A1)=15)*"00:01","00:30")

前にも書きましたが「ツール」→「アドイン」→「分析ツール」にチェックしないとMROUND関数は使用できませんのでご注意ください

回答No.3

esupuresso

出社時間は「CELING」関数を使います
数値の場所に出社時間のセル番地を入れて、基準値は「TIME(0,15,0)」として「OK」をクリック

退社時間は「FLOOR」関数を使います
数値の場所に退社時間のセル番地を入れて、基準値は「TIME(0,15,0)」として「OK」をクリック

後は数式をオートフィルでコピーすると完了

「CELING」関数は基準値の倍数の近い値に切り上げると言う意味で
「FLOOR」関数は基準値の近い倍数に切り下げると言う意味です

回答No.2

zap35

＃０１です。補足がまだありませんが、推測して回答します。
退社時刻が15分刻みであれば、一般的には切り捨てるので
　00分～14分　→　00分
　15分～29分　→　15分
　30分～44分　→　30分
　45分～59分　→　45分
となると思います。これは以下の式になります
　=FLOOR(A1,"00:15")

でも質問者さまの会社では
　00分～14分　→　00分
　15分～29分　→　15分
　30分～44分　→　30分
　45分　　　　→　45分
　46分～59分　→　00分　（←ここが異なる）
なのでしょうか。46分～59分だけが切り上げになりますね。この場合は
　=FLOOR(A1+(MINUTE(A1)>=46)*"00:15","00:15")
になります。意味は「分が４６以上のときだけ１５分加算してから、１５分刻みに切り捨てる」です。
この式では
　16:44　→　16:30
　16:45　→　16:45
　16:46　→　17:00
　17:14　→　17:00
　17:15　→　17:15
になります。

46分～59分が切り上げになるのが「毎時」であれば上の式でよいですが、「16:46～16:59」だけが切り上げでその他の時間帯は切り捨てルールに戻る（例　17:50は17:45に切り捨てる）ようなことはないですか？
もしそうならば、式は
　=FLOOR(A1+AND(A1>="16:46",A1<"17:00")*"00:15","00:15")
になるでしょう。

なお質問に書かれている式は「３０分刻みで切り捨て、切り上げを行う」動作をします。関数ではMROUND関数と同じ動きです。（17:06→17:00、17:07→17:00、17:08→17:15、17:09→17:15）

MROUND関数は「ツール」→「アドイン」→「分析ツール」にチェックしなければ使えませんので、前の回答者さまはそれを意識して何もしなくても使えるROUND関数で記述されたのかもしれません。

回答No.1

zap35

補足要求です。前回の質問はこれですね。
http://oshiete1.goo.ne.jp/qa3384132.html

でも前回の質問を読んでもよく分かりません。以下の例のようにもっと具体的に書いていただけませんか？　毎時15分～44分の扱いが全く説明されていません。
質問にある式では結果が３０分刻みとなり、「出社時間も退社時間も15分単位」とは合致しないように思います

実退社時刻　→　読替後退社時刻
16:40　　　　→　16:45？　16:30？　（？は疑問なところ）
16:44　　　　→　16:45？　16:30？
16:45　　　　→　17:00
17:14　　　　→　17:00
17:15　　　　→　17:15？
17:20　　　　→　17:15？　17:30？
17:25　　　　→　17:15？　17:30？
17:35　　　　→　17:30？　17:45？

補足

ありがとうございます。そうですね、だいぶ説明不足でしたね。私自身もなんだか頭の中がこんがらがってしまいました。
希望としては
16:44　　　　→　16:30
16:45　　　　→　16:30
16:46　　　　→　17:00
17:14　　　　→　17:00
17:15　　　　→　17:30
17:20　　　　→　17:30
17:25　　　　→　17:30
17:35　　　　→　17:30　
としたいのです。
このような内容でわかりますか。
よろしくお願いいたします。