<Excel>時間の差異を30分単位で丸めたいのですが…

質問

シフトの勤務開始および終了時間と、実際の出退勤時間の差異を30分単位で
丸めたいのですが、どうしてもうまくいきません。

シフト時間をA1・出勤(退勤)時間をA2とし、A3に差異を丸めるとする…
=FLOOR(A1-A2,"0:30")
とすると、差異が0:30ちょうどの時に0:00となってしまいます。
これを、0:30と表示させたいのですが、どうしてもできません。

色々と試しましたが、:30と:59のどちらかが必ずおかしくなります。

＜補足＞
出勤を8時とすると、7:31～8:00は8:00、8:01～8:30は8:30と
30分単位での切り上げになります。
退勤を17時とすると、17:00～17:29は17:00、17:30～17:59は17:30と
30分単位での切り捨てになります。

以上、よろしくお願いします。

回答No.1

Sinogi · ベストアンサー

数式で１分の調整をするなら　min(1)　でいかがですか？

=FLOOR(A1-A2,"0:30")
→=FLOOR(A1-A2+MIN(1),"0:30")
など

出勤と退勤の処理については当方検討していません。

お礼

早々の回答ありがとうございます。

=FLOOR(A1-A2+"0:01","0:30")と入力した時はうまくできなかったんですが
このやり方だと、きちんと30分で丸めることができました。
何が違うのか…(苦笑)。また調べて勉強します。

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

回答No.4

grumpy_the_dwarf

これは分数と小数の変換誤差です。日付や時刻のシリアル値は1日を
1とする数値で扱うので、30分っていうと1/48で0.02083333333333..
と割り切れませんね。Excelの有効桁数は12桁しか無いので、途中で
ぶっちぎられちゃいます。そういう中途半端な数値を比較するんで
すから、0.0416666666666と0.0416666666667でゼロになっちゃうと
かの現象が出てくるんですよ。

解決策その1
24とか48を掛けて、引き算して、丸めて、また割る。

解決策その2
引き算した時に、floorなら1秒足し、seilingなら1秒引いておく。

お礼

回答ありがとうございます。

この変換誤差がやっかいですよね。一応、"0:30"のところを小数点で
表してみたりしたんですが、うまくいかず…でした。
単に基準を数値にするのではなく、掛け算や割り算をしてあげないと
いけないんですね。勉強になりました。

今後の参考にさせていただきます。ありがとうございました。

回答No.3

Paradox8

VLOOKUPで処理した方が簡単だと思いますが。

名前・出勤時間　退勤時間で表を作り
出勤時間と退勤時間の列を変えて、関数を入れれば。

この案件は出勤時間は切り上げ、退勤時間は切り捨てになっているからです。

なのでroundとかで入れ子しても複雑になるだけです。

あまり時間が無く懇切丁寧にかけませんが。
一般的によく使う関数なので、覚えておけば色々利用出来ます。

お礼

回答ありがとうございます。

質問では簡単にしか書きませんでしたが、全体がかなり複雑なシートに
なっていまして…。
出退勤の差異だけでよければ、VLOOKUPでいいのかもしれません。

お時間がない中、回答いただきありがとうございました。今後の参考に
させていただきます。

回答No.2

dr-9

FLOORをMROUNDに変えてみたらどうでしょう？

お礼

回答ありがとうございます。

MROUNDも考えたんですが、分析ツールを使わなくてはいけないので
今回は違う方法を試してみたいと思います。（ほとんどエクセル
初心者の方々も入力するので…）

今後の参考にさせていただきます。どうもありがとうございました。