現在の時間を５分単位で切り捨てで表示したい

質問

会社で退社時間を記した書類を出すのですが、
退社時間を５分単位切り捨てで表示するにはどうしたらいいですか？

やりたいことは
例えば、終了時間が１７：３０だとしたら
現在時間をNOW()で表示させて、１７：３０より前だったら、１７：３０を表示して
１７：３０を超えていたら５分単位で切り捨てして現在の時間を表示したいのです。
例えば、１７：５９ならば、１７：５５になります。

A1にNOW()を入れて、
A2にIF（A1<17:30、17:30,FLOOR(A1,"0:05")）ではエラーがでます。
どうしたらいいですか？

表示形式の問題でしょうか。

回答No.6

bunjii · ベストアンサー

>A1にNOW()を入れて、
>A2にIF（A1<17:30、17:30,FLOOR(A1,"0:05")）ではエラーがでます。
はい、エラーになります。
原因は 17:30 です。
17:30 → "17:30" でエラーは回避できますが目的に合いません。

>どうしたらいいですか？
"17:30"は 1900/1/0 17:30 として評価されます。
A1の値は 2013/9/4 16:30 のようになりますのでA1と"17:30"を比較すると常に"17:30"の方が小さくなります。
A1の値から整数部（日付部分）を除去して比較しないと目的に合いません。
MOD関数で小数部（時刻部分）で比較します。

=IF(MOD(A1,1)<"17:30",INT(A1)+"17:30",FLOOR(A1,"0:05"))

回答No.7

bunjii

回答No.6の訂正です。
IF関数の論理式に誤りがありました。

誤　=IF(MOD(A1,1)<"17:30",INT(A1)+"17:30",FLOOR(A1,"0:05"))
正　=IF(MOD(A1,1)<0+"17:30",INT(A1)+"17:30",FLOOR(A1,"0:05"))

時刻のシリアル値と文字列を直接比較すると正しい判定になりませんでした。
MOD(A1,1) と比較対象は 0+"17:30" とするか TIME(17,30,0) としなければなりません。

動作確認が不十分であったことをお詫びいたします。

回答No.5

MarcoRossiItaly

現在時刻のデータ作製の方法ですが、もしも社員一人ひとりに異なる時刻を異なる行などに記入し、それを記録として残しておくのが目的だとすると、ショートカットキー Ctrl+: で現在の時刻が記入できますね。こうして記入すると、関数ではないので、再計算などでも書き換わったりしないというメリットがありますね。

A1　17:59　　　　　　　　　　　　　　 …… NOW 関数あるいは Ctrl+: あるいは数字をタイプして作製
B1　=max("17:30",floor(a1,"0:05"))　　……記入したセルの書式を「時刻」などに指定

なお B1 の数式においては、No.3 さんが教えてくださっているような「"17:30"*1」という処理は不要です。比較演算子（「=」、「<」など）と並べて書くときは、この処理が必要になります。

回答No.4

keithin

＞１７：３０より前だったら、１７：３０を表示して
＞１７：３０を超えていたら５分単位で切り捨てして現在の時間を表示したい

=MAX("17:30",FLOOR(MOD(NOW(),1),"0:5"))
とかで。

勿論この計算でも17:30じゃダメです。
こういうやり口がもしも納得いかないなら、素直にTIME関数とかTIMEVALUE関数をご利用になるのも良いでしょう。

NOW関数の値は「年月日時分秒」ですから、ただの17時30分と比較しては当然いつでも大きくなります。
上述のようにして時分秒だけ切り取るとか、あるいはNOW()-TODAY()みたいなのもありです。

回答No.3

tsubuyuki

> A2にIF（A1<17:30、17:30,FLOOR(A1,"0:05")）ではエラーがでます。
> 表示形式の問題でしょうか。

いえ、表示形式の問題ではなく、書き方の問題です。

一般的にエクセルで式中に「１７：３０」と入れてしまうと、
これは「１７行目から３０行目」の意味にとられがちです。
確認のため、適当なセルに「=17:30」と入力するときっと「＃ＶＡＬＵＥ」が返ります。
同様に適当なセルに「="17:30"*1」として、表示形式を「時刻」にすると
エラーは発生せず、正しく「17:30:00」が返ってきます。
このように、式中で「時刻」を扱うときには、引用符""で括って「時刻文字列」とし、
何らかの手段でシリアル値にしてやる必要があります。
※上記では「１をかけて」シリアル値に直してやっています。

今回の式の例でも同様に、エラーを回避するには
　　=IF(A1<"17:30"*1,"17:30"*1,FLOOR(A1,"0:05"))
として、「時刻を表す文字列に１をかけて」シリアル値にしてやる必要があります。

とは言え、上記だと返り値が文字列になってしまいます。
更に加えて言うのであれば、
> A1にNOW()を入れて、
ですから、Ａ２は常に「当日の１７：３０」との比較にしてやらないと正しい結果は返りません。

なのでもう一工夫して
　　=IF(A1<TODAY()+"17:30",TODAY()+"17:30",FLOOR(A1,"0:05"))
としてやると、当日の１７：３０と比較して、当日の時刻を返してくれます。
「日付を表す整数をＴＯＤＡＹ関数で取り出し、時刻を表す文字列「"17:30"」を強引に足して」
「日付＋時刻を表すシリアル値」に直して比較してやっています。

Ａ１セルがＮＯＷ関数じゃなく、時刻のみを入力する方式なのであれば、
上の単純な式（「＊１」を使った式）でも対応可能です。

参考までにどうぞ。

追記）
「時刻」と「時間」は厳密には違う意味を持つ言葉です。
まぁ、字をよく読めばきっとわかるとは思いますが、念のため。

回答No.2

m_and_dmp

NO.1です。

基本的には貴殿の考えで良いようです。

A2にIF（A1<17:30、17:30,FLOOR(A1,"0:05")）
を
FLOOR(A1,"0:05")
とすれば5分切り捨てになりますので、

A1<17:30がいけないのではないでしょうか、
時間で17:30 は、17.5/24なので、
A1にはNOW()ではなく、時間だけが入っているとして、
IF（A1<17.5/24、17.5/24,FLOOR(A1,"0:05")）
とすると、期待したとおりの結果になります。
しかし、NOW()の場合は、2013年9月3日12:00 として、時間だけではなく1900年ごろから数えた日数が入っていますので、41,520.5000のような数字になります。時間だけになるように整数部分を引き算する手順[たとえば、X-Int(X) ]を追加するとうまくいくでしょう。
FLOOR関数を使って正解がでるように頑張ってください。

回答No.1

m_and_dmp

A1にNOW()
A2に
=ROUNDDOWN(A1*24*60/5,0)/24/60*5