[h]:mm表示形式の数値をDAY関数から正しく抽出できるようにする方法

Question

現在実験の結果をまとめる集計表を作成しており、
合計ポイントの乖離を出すために日と時、分それぞれの要素取り出して合計ポイントを求めたいものの
DAY関数が正しい数値を抽出できていないため正しい数値が得られません。
※実際のデータから一部抜粋したコード

Sub calresult()

Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws As Worksheet

Set ws = wb.Sheets(1)
Dim mRow As Long
Dim i As Long
Dim tb, x

mRow = ws.Cells(Rows.Count, "B").End(xlUp).Row '最終行取得

tb = ws.Range("B4:G" & mRow) '配列格納

ReDim x(1 To UBound(tb), 0) '格納配列

For i = 1 To UBound(tb) '要素1-最大要素数

    x(i, 0) = WorksheetFunction.RoundDown(((24 * Day(tb(i, 5) + Hour(tb(i, 5)) * tb(i, 4)) + (Minute(tb(i, 5)) / 60 * tb(i, 5)), 0)

Next i

'▼配列出力
ws.Range("H4:H" & mRow) = x

End sub

補足①
実験に掛かった合計時間を入力しており24時間以上かかる場合もあるため、
エクセル上の表記形式は[h]:mmとしています。

補足②
小数点以下切り捨て(ROUNDDOWN)で計算を行うため、
①時間*24 * 数値で計算
②DAY・HOUR・MINUTEでそれぞれの要素を取り出して計算
計算方法は固定(指定)されているため後者のやり方を採用した次第です。
※①と②の計算結果に誤差1が生じる場合があることもあるため

参照先：
①https://teratail.com/questions/239127
②https://docs.microsoft.com/ja-jp/office/vba/api/excel.worksheetfunction.weeknum

②の参照先より抜粋
Visual Basic for Applications (VBA) では、Excel と異なる方法でシリアル日付が計算されます。
VBA では、シリアル日付値 1 は 1900 年 1 月 1 日ではなく、1899 年 12 月 31 日に該当します。
とあるので、恐らくVBA上の仕様が原因と思われます。

以上のことからVBA上でDAY関数使っての計算は難しいと踏まえているのですが、
エクセル上に直接関数を入力する他対策はありますでしょうか。

---

追記①
コードだけでは分かりづらいといった指摘を受けております。
そもそもVBA上の仕様が原因だと私の思い込みが原因となります。
実際に自分で読み返したところ、抽出的な書き方で分かりづらい点があり大変申し訳ございません。

取り急ぎ以下実際の処理結果ページと検証結果を以下にて記載します。
「DAY関数が正しい数値を抽出できていないため正しい数値が得られません。」とありますが、

試行時間(F列)から日と時と分の要素を取り出して、固定値と掛けて合計値を抽出するといった処理部分となるのですが、
図にもあるように正しくない計算結果(H列)が反映されています。
※エクセル関数の場合、VBA上と同じ処理で行われているにも関わらず正しい結果が表示

▼正しい処理が行われていないということを踏まえ、
それぞれの要素を変数に格納して確認してみました。
4行目 の試行時間(4:53）の場合
Day(4:53) = 30
Hour(4:53) = 4
Minute(4:53) = 53
※分かりやすいように4:53と表記していますが、実際は配列で格納した分を入れています。

検証結果にもあるように、Day関数のところにおいて
本来であれば24時間以下なので0とするところ、30となっていることが
正しい計算が行われていないことの要因と結論に至りました。

今気づいたのですが、
当初僕が思い込んでいたVBA上の仕様が正しいとすると、
31となるところですが、30となっているので検討違いである可能性が高いかも知れません。

Comments

[退会済みユーザー]

コードが何のセルを表しているのか判らないのですが、列の説明を入れるなり実際のセルイメージを貼り付けるなりした方がよいのではないでしょうか。
結局、どういう入力からどういう出力を得たいのですか？正しい数値を抽出できていない、正しい数値が得られません、だけでは赤の他人からは判りません。（何が正しいのかはあなたしか知らない為）
具体的に説明してください。（今は〇〇という入力に対して××という出力になるが、△△としたい等）

[質問するときのヒント] https://teratail.com/help/question-tips

[quark87139]

指摘頂きありがとうございます。
読み返してみると、仰るように確かに説明が抽出的だったので分かりづらかったです。
指摘頂いた内容を踏まえ、再度加筆させていただきます。

Answer 1

実際のところは、経過日数が欲しいんですよね？
VBAのDay関数は、日付型の日の部分を取得する関数なので、質問の用途には使えないと思います。
（カレンダーで0日という表現は存在しない為）

Day(CDate("2021/03/31 23:56"))⇒31
Day(CDate("2021/04/02 23:56"))⇒2

経過日数なら、DateDiffで行けるのではないかなと。

Debug.Print DateDiff("d", 0, Range("A1").Value) '← A1に"4:53"を設定
Debug.Print DateDiff("d", 0, Range("A2").Value) '← A2に"92:15"を設定

(出力結果)
0
3

Comments

[quark87139]

ありがとうございます。
正確なことを言うと、経過日数ではなく固定値と時間をかけた合計値が欲しいです。
僕が取り入れようとしているものは実験の結果ですので、一般的なものではないかもしれませんが、
分かりやすい例であげると給与計算(時給×合計時間)と同じようなものでしょうか。

またほぼ解決しているのですが、
指摘頂ている通り、
そもそも24時間以下の場合、Day関数は戻り値が30とDay関数は利用できません。
それを理解せず利用していた僕の落ち目でもありますので混乱を招き申し訳ございません。。

結論シリアル値の特性を利用し、Int関数で0か1以上かを判別して、
Day関数を含めた計算式と含めない計算式を取り入れるということに至りました。

[退会済みユーザー]

時間換算であれば、"h" を指定してDateDiffすればよいです。用途として不適当なので、Day関数は含めるべきではありません。

[quark87139]

ありがとうございます。
仰るようにそもそも用途からして不適当なので、
Day関数を含めるのは推奨されないですね。

そもそもエクセル関数だと普通に利用できているからDay関数は時間からでも取得できるという
思い込みから発したミスとなりますので、以後このようなことがないよう特性も理解したうえで
作成するように心掛けたいとおもいます。

丁寧に説明頂きありがとうございました。

Answer 2

②の参照先より抜粋

Visual Basic for Applications (VBA) では、Excel と異なる方法でシリアル日付が計算されます。
VBA では、シリアル日付値 1 は 1900 年 1 月 1 日ではなく、1899 年 12 月 31 日に該当します。
とあるので、恐らくVBA上の仕様が原因と思われます。

以上のことからVBA上でDAY関数使っての計算は難しいと踏まえているのですが、

これは関係ないと思います。

1900/2/28以前はVBAとエクセルで1の差が出ます。下記で確認してみてください。

セルに 1900/2/28 と入力して、表示書式を数値にすると 59 とシリアル値が表示されます。
VBAで、 MsgBox(CLng(#1900/2/28#)) とすると 60 と表示されます。

次にセルに、1900/3/1 と入力して、表示書式を数値にすると 61 とシリアル値が表示されます。
VBAで、 MsgBox(CLng(#1900/3/1#)) とすると 61 と表示されます。

ズレがなくなりました。不思議な現象ですね。原因はエクセルの1900/2/29問題です。

存在しないはずの1900年2月29日が存在する:Excel(エクセル)の関数・数式の使い方/日付計算

ややこしい話ですが、1900/3/1以降の日付の扱いなら、シリアル値が1ずれるということはないです。

ですので、1900/3/1以降ならDay関数を使ってはなんら問題はないです。
つまり、

DAY関数が正しい数値を抽出できていないため正しい数値が得られません。

この認識は間違いです。正しい数値が得られない原因は他にあると思います。

とりあえず、セルに入力されている日付、VBAの計算結果、想定する正しい数値を提示してもらえませんか。それがないと、原因がなにか他者には特定できません。

Comments

[quark87139]

丁寧で尚且つ分かりやすい説明ありがとうございます。
また当初僕の説明が抽出的なところが多く、上手く伝わっていなかったようなので
追記しましたので、ご確認頂けますでしょうか。

VBAの仕様だと思っていた部分が見当違いである場合、
以下の参照先が該当するようなな気もしてきました。
「初期化していないバリアント型変数を指定した場合は30が戻り値」

http://www.openreference.org/articles/view/258

皆様からの指摘を受けて思い浮かんだ処理案としては、
ifを使って23時59分55秒以下であるかどうか判別して、
Day関数の無い計算式とDay関数のある計算式を並べる感じでしょうか。

※念のため補足ですが、参照図の方はサンプルで入力していますが、
　試行時間が24時間を超える場合もあるため、Day関数を利用している次第です。

[hatena19]

24時間未満なら 0、24時間以上なら 1 としたいということですね。
ワークシート関数のDAY関数ならそうなりますので。
ならば、
シリアル値では、24時間未満ということは1未満（小数のみ）で、24時間以上は１以上ですので、Int関数を使えばいいでしょう。

Day(tb(i, 5)) → Int(tb(i, 5))

[quark87139]

hatena さん

ありがとうございます！
シリアル値は24時間未満であれば1未満、24時間以上であれば1となる特性をうまく利用して
Int関数を利用するということですね。。
あまりにも低レベルな質問をしてしまい申し訳ない気持ちでいっぱいです。。

取り急ぎ、指摘頂いた内容を取り込んで修正してみました。
For i = 1 To UBound(tb) '要素1-最大要素数
e = tb(i, 2)
a = Day(e)
b = Hour(e)
c = Minute(e)

If Int(e) <= 1 Then
x(i, 0) = WorksheetFunction.RoundDown((b * tb(i, 1)) + (c / 60 * tb(i, 1)), 0)
Else
x(i, 0) = WorksheetFunction.RoundDown(((24 * a + b) * tb(i, 1)) + (c / 60 * tb(i, 1)), 0)
End If
Next i

[hatena19]

Intは小数点以下切り捨てなので、WorksheetFunction.RoundDown も Int でいいかと。
あとIf分岐も必要ないですね。

For i = 1 To UBound(tb) '要素1-最大要素数
　　e = tb(i, 2)
　　a = Int(e)
　　b = Hour(e)
　　c = Minute(e)
　　x(i, 0) = Int(((24 * a + b) * tb(i, 1)) + (c / 60 * tb(i, 1)))
Next i

[hatena19]

エクセル関数の式では正しい値なので、その式を埋め込んで、値代入すればループする必要もないです。

With ws.Range("B4:G" & mRow)
　　 .Formula = "=ROUNDDOWN(((24*DAY(F4)+HOUR(F4))*E4)+(MINUTE(F4)/60*E4),0)"
　　.Value = .Value
End With

[quark87139]

hatena さん

ありがとうございます！
よく言われてみればIntは小数点以下切り捨てなので、代用できますね。
2つ目にて頂いてる回答についてですが、
当初もしどうしても無理な場合は前回でも教えて頂いたセルに直接エクセル関数を埋め込んで値代入する
やり方を採用するつもりでした。

何から何までとご丁寧な説明頂きありがとうございました。
結論、どちらのやり方でも理想の数値を取得することができましたことをご報告させていただきます。

Answer 3

よくわからないけどDay(1 + tb(i, 5))でよいのでは。

Comments

[quark87139]

ご回答ありがとうございます。
当初僕もそのように考えていました。
結論、24時間以上の場合だとそれでも良いと思うのですが、、
24時間以下の場合だとずれませんか？

24時間以上 = +1
24時間以下 = 1日以下なので0