
いつもお世話になります。
Excelで勤務実働時間の変換作業を行ってます。
元のデータは"文字列"で表示されてて、"=TIMEVALUE(A1)"を使って
12:1 → 12:01 と表示させてます。
そこで質問ですが、"24:1"以上の場合ですが、"TIMEVALUE"を使うと当然"00:01"に表示されるので、
=IF(TEXT(LEFT(A1,2),"#,###")>="24",TIMEVALUE(A1)+"24:00"
と数式を入れました。続いて、同じ様に"IF"を並べて">=96"、">=72"、">=48"と作ったのですが、今度は"100:00"以上の場合です。同じく
=IF(TEXT(LEFT(A1,3),"#,###")>="120",TIMEVALUE(A1)+"120:00",IF(TEXT(LEFT(A1,3),"#,###")>="100",TIMEVALUE(A1)+"96:00"を追加してみたのですが、そうすると今度は
"100:1"以上はうまく表示できたのですが"36:1"以上"99:59"いかが正しく表示されなくなりました。中身を確認すると"=TEXT"表示に問題があるようです。
長々と説明させていただきましたが、ようするに、3桁~6桁の文字列表示の時刻を"TIMEVALUE"に表示させ、それを元に関数を使ってまた別の計算式を作りたいのです。
解りにくい内容ですみませんがよろしくお願いします。
No.5ベストアンサー
- 回答日時:
解答No2です。
確かに文字列としてA1セルに24:10と入力すれば=TIMEVALUE(A1)での表示は0:10と表示されセルの表示形式を[h]:mmとしても24:10と表示されませんね。そこでいろいろと苦労されているのでしょう。次のようにすることで解決すればよいでしょう。=DATEVALUE(A1)+TIMEVALUE(A1)
もちろんセルの書式設定では[h]:mmのようにします。
これでA1セルに文字列として24:10と入力されれば24:10と、37:00と入力されれば37:00と表示されます。もちろんこのようにして変換した時刻は計算に使うことができ、SUM関数でも正常に使うことができます。
大変、大変遅くなって申し訳ありません。m(--)m
もう、誰も答えてくれないかと半ば諦めてました。他の作業もありそちらに気をとられながら、また時間作業に戻って"教えてgoo"を確認してみたら!!皆さんちゃんともう一度回答をくださってて感謝感謝です。
すごく簡単で解りやすい数式で感動しました。
ありがとうございました!!
No.4
- 回答日時:
#1です。
再度ご確認いただけないでしょうか?
私の回答で使用している関数は
TIMEVALUE ではなく、VALUE です。
TIMEVALUE 関数を使用した場合は、おっしゃる通り
24:00以上の値を扱うことができずに
24:1 は 00:01 として扱われてしまいますが、
VALUE 関数を使用することにより
24:1 は 24:01 として扱うことができ、
書式設定 [h]:mm により表示も 24:01 となります。
もちろん、SUM関数による計算等も可能です。
(動作確認済み)
大変、大変遅くなって申し訳ありません。m(--)m
半ば諦めていたもので…
ホントです。大きな勘違いをしてました。すみません。
"TIME"が付かないだけでこんなに一瞬でできるのですね。。。私の苦労はなんだったのか…
ありがとうございました!!すっきりです!!
No.3
- 回答日時:
これでどうでしょうか?
=TIMEVALUE(A1)+CONCATENATE(24*(ROUNDDOWN(VALUE(MID(A1,1,FIND(":",A1)-1))/24,0)),":00")
この回答への補足
大変、大変遅くなって申し訳ありません。m(--)m
もう、誰も答えてくれないかと半ば諦めてました。
確かにこちらの数式でもなります!!見たことない関数を利用されてるので今後の参考にもなりました。
ありがとうございました!!
No.1
- 回答日時:
使用する関数と書式設定をすることで
とても簡単にご希望の表示をすべて実現できます。
まず、式はたったこれだけ。
=VALUE(A1)
そうすると、場合分けでの面倒な計算もまったく不要です。
しかしこれでは、やはり24:00以降は正しく表示されないので
書式設定で正しく表示されるように変更します。
セルの書式設定の表示形式からユーザー定義を選択し
[h]:mm
と設定してみてください。
もちろん、これを元に関数を使ってまた別の計算式を作ることも可能です。
この回答への補足
それではだめなのです。
セルの書式設定の表示形式からユーザー定義を選択し
[h]:mm
に設定してるんです。試してみてください。
セルA1を書式設定の表示形式から"文字列"に設定し、"25:1"と入力し
セルB1に書式設定の表示形式から"[h]:mm
"に設定し"=VALUE(A1)"を入力してください。"1:01"になりませんか。
回答No.2,KURUMITO様の答えは"=SUM()"等の関数式が使えません。"=A1+A2"なら計算できますが。。
良いお知恵を。。。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) If関数に関する質問です。(再掲) 3 2022/10/01 20:51
- Excel(エクセル) エクセルの数式で教えてください。 2 2022/10/25 17:10
- Visual Basic(VBA) ExcelのVBAコードについて教えてください。 1 2023/08/09 10:33
- Excel(エクセル) エクセルの関数に関しての質問です。 5 2022/10/07 11:17
- Excel(エクセル) エクセルでIF関数中にIFERROR関数を使いたいのですが???? 5 2022/04/08 13:24
- Excel(エクセル) 下記エクセルの式がなぜこうなるのか理由が知りたいです。 6 2022/08/20 00:43
- Excel(エクセル) エクセル 関数について質問です。 2 2022/10/03 11:14
- Excel(エクセル) エクセルの数式で教えてください。 5 2023/02/10 15:11
- Excel(エクセル) エクセルの数式で教えてください。 1 2023/02/15 08:30
- Visual Basic(VBA) if関数とifs関数は組み合わせることはできますか。 セルA1が「A」のとき「向日葵」と表示。 セル 4 2023/02/02 20:48
このQ&Aを見た人はこんなQ&Aも見ています
-
Excel書式設定が24時間以上の設定の時の数式バーの内容について
財務・会計・経理
-
31:30:00が1900/1/1 7:30:0
その他(Microsoft Office)
-
vba 時間の引き算 例えば 15:00から16:15の間の時間は1時間15分ですが、vbaのコード
Visual Basic(VBA)
-
-
4
エクセルで表示形式の時刻の「0:00」を表示しないようにするには?
Excel(エクセル)
-
5
エクセル関数/10進法から60進法への変換(カンマ表示)
Excel(エクセル)
-
6
EXCELで2つの数値のうち大きい方を採択する数式
Excel(エクセル)
-
7
EXCELで特定のセルに表示された項目をヘッダーやフッターに出力するには
Excel(エクセル)
-
8
Excelでセル参照したとき、書式も一緒に持ってくるには?
Windows Vista・XP
-
9
エクセル マクロ 24時間を越える表示
Excel(エクセル)
-
10
エクセルでの5桁の時間の計算方法
Excel(エクセル)
-
11
VBAでダブルコーテーション入りの数式をセルにセットしたい
Visual Basic(VBA)
-
12
IF関数で、時間を条件にしたい場合の式について
Access(アクセス)
-
13
SUMIF関数で、「ブランク以外を合計」を指定したい
その他(Microsoft Office)
-
14
エクセル 1万時間を越える際の時間計算&合計算出
Excel(エクセル)
-
15
エクセルで左から、最初に0より大きい値を抽出したい
Excel(エクセル)
-
16
名前をつけて保存した後、元のファイルに戻るには
その他(コンピューター・テクノロジー)
-
17
VBAでブックを非表示で開いて処理して閉じる方法
Excel(エクセル)
-
18
条件付書式で「=#N/A」に色を付けたい
Excel(エクセル)
-
19
[IF文] 「何時〜何時」で該当する時間を表示したい お疲れ様です。お知恵をお借りできればと思います
Excel(エクセル)
-
20
Excel VBA で時刻の24時間表示がわかりません
その他(Microsoft Office)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで表示形式の時刻の「0...
-
エクセルのパーセンテージで、...
-
CSV形式にすると出てくる空白を...
-
小数点の数値をそのままセルに...
-
差し込み印刷で小数点以下がた...
-
エクセルで1ケタ数字に全て000...
-
グーグルスプレッドシートで6.5...
-
エクセル(▲マイナス表示したい)
-
エクセルの時間計算でコロン(...
-
Excelで、小数点以下が0になる...
-
エクセルで四捨五入した数値に...
-
エクセル(Excel)で、数値を一定...
-
エクセルの指数近似曲線の式か...
-
エクセルの時間計算表示で、( 0...
-
24時間以上の表示
-
エクセルで上2桁をカットして...
-
エクセルの参照(コピー)での...
-
Excelで時間の合計値がおかしい...
-
Accessデータをテキスト変換す...
-
エクセル3桁区切りの数値を通...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで表示形式の時刻の「0...
-
エクセルのパーセンテージで、...
-
CSV形式にすると出てくる空白を...
-
グーグルスプレッドシートで6.5...
-
エクセル(Excel)で、数値を一定...
-
エクセルで四捨五入した数値に...
-
Excelで、小数点以下が0になる...
-
エクセルで上2桁をカットして...
-
エクセルで1ケタ数字に全て000...
-
エクセル(▲マイナス表示したい)
-
エクセルの指数近似曲線の式か...
-
小数点の数値をそのままセルに...
-
エクセルの時間計算でコロン(...
-
エクセル2003で並べ替えす...
-
エクセルで分と秒だけを記録、...
-
24時間以上の表示
-
【Excel】負の時間を入力する方法
-
エクセルで¥5,000-と表示...
-
エクセル3桁区切りの数値を通...
-
エクセルの時間計算表示で、( 0...
おすすめ情報