- ベストアンサー
VBAによる時間変換及び時間計算
勤務表を作成しているのですが、書式変換及び残業計算できません。 (前提) Web勤怠システムの数値をコピー&貼り付けをしてエクセルへ転記しています。なので、書式が文字列になってしまいます。 A1==>19:40 ・・・書式は時刻 A2==>20:02 ・・・書式は文字列 Range("A3") = (TimeValue(Range("A2")) - Range("A1")) / 24 これでは、上手くできません。 他の方法(式)を、教えて下さい。 合わせて、6分未満は切り捨ての式を含めてくれると嬉しいです。
- みんなの回答 (5)
- 専門家の回答
質問者が選んだベストアンサー
どのようにうまく行けないかが分からないため正確な回答はできませんが、残業時間を6分単位の時刻形式で取得しようとしているが小数点付きの数字が表示される等して目的の結果を表示できないという風な推測の元、回答いたします。 まず、基本的な部分で >Range("A3") = (TimeValue(Range("A2")) - Range("A1")) / 24 ですが、A3にも日付の書式を与えると正しい結果が表示されます。(分単位ですと書式に"[m]"と指定してみてください) ただ、質問の内容に >合わせて、6分未満は切り捨ての式を含めてくれると嬉しいです。 とあることと、VBAでの回答でいいことから、プログラムを大きく改変させていただきます。 ---------------------------ここから--------------------------- Dim ZangyoMinutes As Integer '分単位の残業時間を格納する変数 Const MinimumTime As Integer = 6 '切り落とす分単位の設定 '差分の時刻を分単位で取り出す(A2>A1の場合に正になる) ZangyoMinutes = DateDiff("n", CDate(Range("A1").Value), CDate(Range("A2").Value)) '求めた分単位の残業時間を6分単位で切り落とす ZangyoMinutes = Int(ZangyoMinutes / MinimumTime) * MinimumTime '代入する前にA3の書式を0:00形式に指定する Range("A3").NumberFormat = "h:mm" 'A3に結果を代入する Range("A3").Value = TimeSerial(0, ZangyoMinutes, 0) ---------------------------ここまで--------------------------- TimeValue関数の変わりにCDate関数を使用しています。基本的な動作は変わりませんが、TimeValue関数がVariant型を返すのに対してCDate関数はDate型を返すのが違いです。 まず、DateDiff関数が2個の時間の差を年・月・日・時・分・秒単位で求められる関数です。"n"が分に相当しますのでこの部分で残業時間を分単位で取得します。 続いて、一定間隔数字ごとに切り落とす式が Int([元の数字]/[間隔])*[間隔] になります。 あとは、表示したい方法でセルに表示をするわけですが、分のままでしたらそのまま Range("A3").Value = ZangyoMinutes でもいいですし、Excelの時刻の形式にしたいのであればTimeSerial関数を使用する感じですね。 TimeSerial([時],[分],[秒]) 時・分・秒の3個は共に省略不可ですが、59を超えた数字を入れた場合は次の桁を繰り上げてくれるので何かと便利な関数です。 最後に、代入する前に書式を設定し、設定が終わったら値を代入すると完成です。(必ず書式設定を先に行ってください) 最後に .Value .NumberFormatなどプロパティーはきちんと明示しましょう。
その他の回答 (4)
- Wendy02
- ベストアンサー率57% (3570/6232)
こんばんは。 すでに、回答は出ていますが、「書式は文字列」というのは、そこを参照するセルも文字列にしてしまうので、EXCELでは、単独で使うセルのみであって、書式に、A1が、時刻設定で、A2が、文字列設定というのはまったくうまくないです。マクロ以前の問題です。だから、書式をマクロで直します。 なお、TimeValue を使うなら、Rangeオブジェクトのプロパティは、.Text プロパティですね。 '------------------------------------------- Sub MacroTest1() Dim t1 As Date Dim t2 As Date Dim t3 As Date Dim t4 As Date Dim dif As Long t1 = Range("A1").Value t2 = Range("A2").Value If t2 >= t1 Then t3 = t2 - t1 Range("A1:A3").NumberFormat = "h:mm" '書式を直す dif = Minute(t3) Mod 6 t4 = TimeSerial(Hour(t3), Minute(t3) - dif, 0) Range("A3").Value = t4 End If End Sub '-------------------------------------------
お礼
わざわざ回答有難うございます。 参考資料として、使いたいと思います。 有難う御座いました。
- 有田 賢治(@ARIKEN43)
- ベストアンサー率50% (563/1116)
25630さん 今晩は! ■Excel(エクセル)関数の技:時刻・時間の計算に関する技↓ http://www.eurus.dti.ne.jp/~yoneyama/Excel/waza/jikan01.html 内容:1.時刻/時間のシリアル値・2.時刻/時間の計算・3.時刻/時間の丸め処理・4.シリアル値を数値に直す・5.時刻/時間の比較・6.数値を時刻/時間に変換する などの解説がされています。 ■Excel(エクセル)実用編:時給計算書の作成例↓ http://www.eurus.dti.ne.jp/~yoneyama/Excel/jituyou/jikyu.htm 内容:1.完成例・2.基本数値の入力・3.勤務時間の計算・4.支払時間の計算・5.支給額の計算・6.日付・7.時刻/時間の基本的な計算方法などは別ページで説明しています。 完成例はサンプルファイル(jikyu-01.zip 約20KB)のダウンロードが出来ます。 ■Excel(エクセル)講座 Q&A(Tips):時刻・時間関連↓ http://www.eurus.dti.ne.jp/~yoneyama/Excel/ex-q-a/q_jikoku.html 内容:1.時刻の計算→勤務時間の計算を例として説明します。 2.時刻のシリアル値→時刻のシリアル値は、0~0.99999999の範囲にある値で、0:00:00(午前0時)から 23:59:59 (午後11時59分59秒)までの時刻を表します。 3.勤務時間の計算→パートの人の勤務時間計算で、定時が 9:00~17:00となっている場合の計算方法を考えてみましょう。 上記の時間変換の基本関数を確実に身につけましょう。 回答者のRandomizeさんが親切にノウハウを回答されていますが、プログラムはノウハウの集まりですし同じ結果でも違うコードの書き方が異なります。
お礼
回答有難うございます。 VBA不使用の時は、参考にさせて頂きます。 有難うございました。
- xls88
- ベストアンサー率56% (669/1189)
こういうことでしょうか? Sub test1() Dim tv As Variant tv = TimeValue(Range("A2")) - Range("A1") Range("A3") = (Hour(tv) + Minute(tv) / 60) / 24 End Sub >合わせて、6分未満は切り捨ての式を・・・ 下記でどうでしょうか? Sub test2() Dim tv As Variant tv = TimeValue(Range("A2")) - Range("A1") If Minute(tv) < 6 Then Range("A3") = (Hour(tv)) / 24 Else Range("A3") = (Hour(tv) + Minute(tv) / 60) / 24 End If End Sub
お礼
回答有難うございます。 検証して見ましたが、 答えが0.3にしたいのですが、0.015…になってしまいました。 色々とこちらで試しても出来なかったのですが、他の方の参考等を試したら出来ました。 参考式は、今後に活かしたいと思います。 有難う御座いました。
- KURUMITO
- ベストアンサー率42% (1835/4283)
VBAに取り組む前に数式をよく理解する必要がありますね。 お示しの式ですとA1セルには>19:40とA2せるには>20:02と入力されているように見られますが、それでしたら両セルとも文字列になっているはずですね。時刻では>の入った形では存在しませんね。 ところで仮にそのような>の入った形で入力されているとしたら、それらを用いて式をたてるなどできないはずです。 A3への入力の式は次のようにしてセルの表示形式で時刻にすればよいでしょう。 =SUBSTITUTE(A2,">","")*1-SUBSTITUTE(A1,">","")*1
お礼
回答有難うございます。 >は、矢印のつもりでしたが、 最後の式は、別の場所で参考にさせて頂きたいと思います。 有難う御座いました。
お礼
回答有難うございます。また、親切に式の説明してくれて大変有難く思っています。 おかげさまで、思い通りになりました。 大変勉強になり、有難う御座いました。