いつもお世話になります。
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で質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
都道府県穴埋めゲーム
都道府県の名前を1人1つずつ投稿してください。全ての都道府県が出たら締め切ります!
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
vba 時間の引き算 例えば 15:00から16:15の間の時間は1時間15分ですが、vbaのコード
Visual Basic(VBA)
-
エクセル マクロ 24時間を越える表示
Excel(エクセル)
-
31:30:00が1900/1/1 7:30:0
その他(Microsoft Office)
-
-
4
Excel VBA で時刻の24時間表示がわかりません
その他(Microsoft Office)
-
5
Excel条件付書式(残業45時間以上になったら色をつけたい)
Excel(エクセル)
-
6
エクセルで表示形式の時刻の「0:00」を表示しないようにするには?
Excel(エクセル)
-
7
IF関数で、時間を条件にしたい場合の式について
Access(アクセス)
-
8
IF関数で空欄(")の時、Nullにしたい
その他(Microsoft Office)
-
9
Excelでの時間計算 条件付き書式
その他(Microsoft Office)
-
10
EXCEL VBAで全選択範囲の解除
Excel(エクセル)
-
11
エクセルVBAのエディターが上書きモード?に・・・・。
Excel(エクセル)
-
12
エクセルVBAで5行目からオートフィルタモードに設定したいたい
Excel(エクセル)
-
13
Accessで24時間を超える時間の入力ができない。
Excel(エクセル)
-
14
Excel マクロ VBA プロシージャが大きすぎます のエラー対処方法
Visual Basic(VBA)
-
15
エクセル関数/10進法から60進法への変換(カンマ表示)
Excel(エクセル)
-
16
[MS Access]クエリで変換 hhmm(数値型)⇒hh:mm(日付/時刻型)
その他(Microsoft Office)
-
17
Excel書式設定が24時間以上の設定の時の数式バーの内容について
財務・会計・経理
-
18
ピボットテーブルでは時間の表示が出ない
Excel(エクセル)
-
19
分を時間に変換するプログラム
Visual Basic(VBA)
-
20
エクセルの時間計算でコロン(:)を省略した定義を設定し、時間差分の計算
その他(Microsoft Office)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで表示形式の時刻の「0...
-
エクセルのパーセンテージで、...
-
CSV形式にすると出てくる空白を...
-
エクセルで分と秒だけを記録、...
-
エクセルで1ケタ数字に全て000...
-
Excelで、小数点以下が0になる...
-
エクセルで四捨五入した数値に...
-
エクセル(▲マイナス表示したい)
-
エクセルの時間計算でコロン(...
-
エクセルで上2桁をカットして...
-
Accessデータをテキスト変換す...
-
グーグルスプレッドシートで6.5...
-
エクセルを使用して、円周率を...
-
エクセルの時間計算表示で、( 0...
-
小数点の数値をそのままセルに...
-
エクセル3桁区切りの数値を通...
-
秒以下の表示
-
エクセルで00を表示するには?
-
エクセルで『2,000,000』を『20...
-
エクセルの指数近似曲線の式か...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで表示形式の時刻の「0...
-
エクセルのパーセンテージで、...
-
CSV形式にすると出てくる空白を...
-
グーグルスプレッドシートで6.5...
-
エクセル(▲マイナス表示したい)
-
Excelで、小数点以下が0になる...
-
エクセルで1ケタ数字に全て000...
-
エクセルの時間計算でコロン(...
-
エクセルで分と秒だけを記録、...
-
エクセルで上2桁をカットして...
-
エクセルを使用して、円周率を...
-
エクセルで四捨五入した数値に...
-
エクセル3桁区切りの数値を通...
-
エクセルで¥5,000-と表示...
-
Accessデータをテキスト変換す...
-
エクセルの時間計算表示で、( 0...
-
24時間以上の表示
-
エクセルでエクセル 年齢の計算...
-
エクセル(Excel)で、数値を一定...
-
エクセル2003で並べ替えす...
おすすめ情報