タイトルのとおり、負の時間になった場合に0:00と表示したいのですが、なかなかうまくいきません。
エクセルにて勤務の遅刻・早退の表を作っています。8:30までにこなかった場合に超過時間を表示し(9:00にきた場合には0:30)、17:00より前に帰った場合に過不足時間を表示、という表を作っています。
http://oshiete1.watch.impress.co.jp/qa4183597.html
この過去の質問を参考にやってみましたが・・・うまくいきません;
現在、A1にある元データの数字(例:815)を拾う為に、セルのA3に
=IF(A1="","",TEXT(A1,"0!:00"))
という数式が入れてあります。
A4のセルには
=IF(ISBLANK(A3),"",IF(A3>TIMEVALUE("8:30"),A3-"8:30",0))
という数式が入れてあります。
A4のセルに負の時間であれば0:00と表示、という関数を入れれば出来そうなのですが、試行錯誤してもわかりませんでした。
ご教示いただけたらと思い投稿しました。
宜しくお願いします。
No.3ベストアンサー
- 回答日時:
> =IF(A1="","",TEXT(A1,"0!:00"))
これは時刻じゃなくてただの文字列ですね。これを
> =IF(ISBLANK(A3),"",IF(A3>TIMEVALUE("8:30"),A3-"8:30",0))
この式に入れたら、「A3>TIMEVALUE("8:30")」は常に真です。文字
列は数値より大きいことになってますから。四則演算では勝手に型
を合わせるExcelも、比較演算子では型を意識する必要があります。
というわけでA3を
=if(A1="","",timevalue(text(A1,"0!:00")))
として比較する値同士の型を合わせて下さい。
>> =IF(A1="","",TEXT(A1,"0!:00"))
>これは時刻じゃなくてただの文字列ですね。これを
これだと時刻として扱われず、文字列を時刻のように表示している・・ということであってますでしょうか?
こちらの式でも出来ました!こんな短くてOKだったとは目から鱗です。ありがとうございます。今後は似た型を意識してみたいと思います。
No.7
- 回答日時:
解答No4です。
B3が空白のときにB4を空白にするのでしたらIF(ISBLANK(B3),"",のような式にするのは間違いです。B3セルには見かけ上空白になっていても数式が入っていますのでBLANK(空白)ではありません。IF(B3="","",のように使うことが必要です。
確認のために他のセルに=ISBLANK(B3)と入力してみましょう。答えはFALSEとなります。B3セルに式がある以上、空白としては認識されません。
この回答への補足
なるほど・・・BLANKは完全に空白の時に使うべきものだったんですね。勉強になります。
それと同時に修正を加えてみたところ、無事解決できました!
皆様ご丁寧にありがとうございました。
とてもたすかりました!
No.5
- 回答日時:
B4セルの表示形式を、 ユーザ定義 で h:mm;h:mm;
にしてみてください。
表示形式は、 正の場合; 負の場合; 零の場合
です。
この回答への補足
消えました!・・・しかし問題が;;
私の説明が足りませんでした。
遅刻・早退をしていない場合には0:00をつけています。
全部選んでユーザ定義を変えたところ、正しく0:00が表示されてる場所も消えてしまいました。
やはり1個1個選択してやるしかないのでしょうか?
No.4
- 回答日時:
簡単な式は次のようになりますね。
A4セルへの入力式は
=IF(A3="","",IF(A3*1>"8:30"*1,A3-"8:30",0))
1をかけることでA3が文字列としての時刻をシリアル値に変換します。TIMEVALUE関数を使うのと同じ意味になります。
A3ではText関数を使っていますのであくまでも文字列で表した、例えば8:15にすぎません。シリアル値に直して比較に使うことが必要です。
この回答への補足
すみません、質問は解決したのですが・・・また新たな問題が。
こちらに補足させていただきます。
タイトルのとおり退勤時間の計算もしてるのですが、B1には退勤時間(例1759)を入力してあり、同じようにB3、B4にも出勤と同じように数式が入れてあります。
参照したB1セルが空白だった場合に、B3のセルにはきちんと空白が表示されるのですが、B4に0:00が表示されてしまいます・・・。
B1が空白の場合は空白で表示したいのですが、セルの関数に関わらず値がなければ空白にする、といったようなことは出来るのでしょうか?
調べてみましたがISBLANKをうまく使えてないのか、わかりませんでした。
無知ですみません・・・おわかりになるかたはいらっしゃるでしょうか?
1をかけるとシリアル値になるというのは初めて聞きました・・・奥が深い
シリアル値に直さないといけない、ということですね。とても勉強になりました!
遅くなりましたが皆様丁寧にありがとうございました。おかげで解決いたしました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) ユーザー定義について質問です。 2 2023/06/28 13:21
- Excel(エクセル) 出退勤管理の遅刻・早退時間について 3 2023/08/10 15:33
- Excel(エクセル) <スプレッドシート>IF関数の複数条件について 5 2022/10/27 14:38
- Excel(エクセル) エクセルの数式で教えてください。 2 2022/10/25 17:10
- Visual Basic(VBA) if関数とifs関数は組み合わせることはできますか。 セルA1が「A」のとき「向日葵」と表示。 セル 4 2023/02/02 20:48
- Excel(エクセル) Excel 特定セルの数値を参照したセルの0表示が空白にならないのはどうしてか? 3 2022/04/28 22:23
- Excel(エクセル) 【EXCEL】=セル&セルが上手く表示できない。 7 2022/09/04 21:32
- Excel(エクセル) 表計算ソフトcalcにおいて、1時間10分を1.1と表記する方法とそれらを集計する方法は? 4 2022/04/06 16:54
- Excel(エクセル) エクセルの数式で教えてください。 5 2023/02/10 15:11
- Excel(エクセル) マクロだと数式が表示される 2 2022/09/10 14:48
このQ&Aを見た人はこんなQ&Aも見ています
-
「どうして捨てられないの?」前妻の物を捨てられない男性の心理って?
前妻の物を捨てられない理由に加え、捨てるための手段はあるのかを専門家に聞いてみた!
-
エクセルで、時間の計算をした場合、マイナス表示を0:00にする方法を教えてください。
Excel(エクセル)
-
エクセルで時刻の計算結果が-0:00となってしまう
Excel(エクセル)
-
エクセルで表示形式の時刻の「0:00」を表示しないようにするには?
Excel(エクセル)
-
-
4
エクセル マイナスなら0と表示したい。
Excel(エクセル)
-
5
エクセルで、時間 0:00を表示する方法
Excel(エクセル)
-
6
エクセルで時間表示を負(-)で表示方法
Excel(エクセル)
-
7
Excelの時間計算で経過時間から特定の時間を引く方法
Excel(エクセル)
-
8
excelで時間の引き算でマイナス表示にしたい
その他(Microsoft Office)
-
9
31:30:00が1900/1/1 7:30:0
その他(Microsoft Office)
-
10
IF関数で、時間を条件にしたい場合の式について
Access(アクセス)
-
11
Excelで負の数を足さずに0以上の数字だけを足したい
Excel(エクセル)
-
12
ある一定時間を超えた場合の超えた時間のみを合計する方法をご存知でしたら
Excel(エクセル)
-
13
複数の休憩時間がある場合の休憩時間の算出方法
Excel(エクセル)
-
14
エクセルで時間の判定をしています。0時以上5時以下の場合は○と判定したいので、以下のイフ関数を試しま
Excel(エクセル)
-
15
エクセルの書式設定に時間(0:00)を使いたい
Excel(エクセル)
-
16
エクセル 特定の文字を入れると他のセルの色が変わる
Excel(エクセル)
-
17
エクセル0:00と表示される原因
Excel(エクセル)
-
18
EXCELで2つの数値のうち大きい方を採択する数式
Excel(エクセル)
-
19
エクセルで#####が表示されてしまう
Excel(エクセル)
-
20
Excelにて勤務表の作成で早出・残業、所定労働時間の計算式を教えて下さい
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
IF関数で空欄("")の時、Null...
-
関数TRANSPOSEで空白セルを0に...
-
ピボットテーブルで空白セルの...
-
エクセルでCSVを編集するとき、...
-
Excel > ピボットテーブル「(空...
-
エクセルで上の行の値を自動的...
-
数式による空白を無視して最終...
-
「データ要素を線で結ぶ」がチ...
-
空白セル内の数式を残したまま...
-
形式貼り付けの「空白を無視す...
-
excel2010 空白セルにのみ貼り...
-
エクセル 連番が途切れていると...
-
Excel:関数が入っているセルに...
-
色つき行の一括削除は?
-
エクセルのグラフで式や文字列...
-
【Excel】 Ctrl+方向キー で空...
-
Excelで、入力文字の後に自動で...
-
エクセルで、「複数のセルの中...
-
数式の結果が空白の時の空白扱い
-
エクセルにて負の時間を0:00と...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
IF関数で空欄("")の時、Null...
-
数式による空白を無視して最終...
-
エクセルでCSVを編集するとき、...
-
ピボットテーブルで空白セルの...
-
Excel > ピボットテーブル「(空...
-
関数TRANSPOSEで空白セルを0に...
-
excel2010 空白セルにのみ貼り...
-
「データ要素を線で結ぶ」がチ...
-
Excel:関数が入っているセルに...
-
Excelで、入力文字の後に自動で...
-
空白セル内の数式を残したまま...
-
色つき行の一括削除は?
-
エクセルで、「複数のセルの中...
-
エクセル 連番が途切れていると...
-
エクセルで上の行の値を自動的...
-
SUMIFS関数で「計算式による空...
-
【Excel】 csvの作成時、空白セ...
-
《Excel2000》SUMPRODUCT関数で...
-
エクセル セルのコピー元が空...
-
空白を0とみなす関数
おすすめ情報