VBAによる時間変換及び時間計算

質問

勤務表を作成しているのですが、書式変換及び残業計算できません。

（前提）
Ｗｅｂ勤怠システムの数値をコピー＆貼り付けをしてエクセルへ転記しています。なので、書式が文字列になってしまいます。

A1==>19:40 ・・・書式は時刻
A2==>20:02 ・・・書式は文字列

Range("A3") = (TimeValue(Range("A2")) - Range("A1")) / 24
これでは、上手くできません。
他の方法（式）を、教えて下さい。

合わせて、６分未満は切り捨ての式を含めてくれると嬉しいです。

回答No.3

Randomize · ベストアンサー

どのようにうまく行けないかが分からないため正確な回答はできませんが、残業時間を6分単位の時刻形式で取得しようとしているが小数点付きの数字が表示される等して目的の結果を表示できないという風な推測の元、回答いたします。
まず、基本的な部分で
>Range("A3") = (TimeValue(Range("A2")) - Range("A1")) / 24
ですが、A3にも日付の書式を与えると正しい結果が表示されます。(分単位ですと書式に"[m]"と指定してみてください)

ただ、質問の内容に
>合わせて、６分未満は切り捨ての式を含めてくれると嬉しいです。
とあることと、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などプロパティーはきちんと明示しましょう。

お礼

回答有難うございます。また、親切に式の説明してくれて大変有難く思っています。
おかげさまで、思い通りになりました。

大変勉強になり、有難う御座いました。

回答No.5

Wendy02

こんばんは。

すでに、回答は出ていますが、「書式は文字列」というのは、そこを参照するセルも文字列にしてしまうので、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
'-------------------------------------------

お礼

わざわざ回答有難うございます。
参考資料として、使いたいと思います。

有難う御座いました。

回答No.4

有田 賢治（@ARIKEN43）

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さんが親切にノウハウを回答されていますが、プログラムはノウハウの集まりですし同じ結果でも違うコードの書き方が異なります。

お礼

回答有難うございます。
ＶＢＡ不使用の時は、参考にさせて頂きます。

有難うございました。

回答No.2

xls88

こういうことでしょうか？
Sub test1()
Dim tv As Variant
tv = TimeValue(Range("A2")) - Range("A1")
Range("A3") = (Hour(tv) + Minute(tv) / 60) / 24
End Sub

＞合わせて、６分未満は切り捨ての式を・・・
下記でどうでしょうか？
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…になってしまいました。
色々とこちらで試しても出来なかったのですが、他の方の参考等を試したら出来ました。
参考式は、今後に活かしたいと思います。

有難う御座いました。

回答No.1

KURUMITO

ＶＢＡに取り組む前に数式をよく理解する必要がありますね。
お示しの式ですとＡ1セルには>19:40とＡ２せるには>20:02と入力されているように見られますが、それでしたら両セルとも文字列になっているはずですね。時刻では>の入った形では存在しませんね。
ところで仮にそのような＞の入った形で入力されているとしたら、それらを用いて式をたてるなどできないはずです。
Ａ3への入力の式は次のようにしてセルの表示形式で時刻にすればよいでしょう。
=SUBSTITUTE(A2,">","")*1-SUBSTITUTE(A1,">","")*1

お礼

回答有難うございます。
＞は、矢印のつもりでしたが、
最後の式は、別の場所で参考にさせて頂きたいと思います。

有難う御座いました。