View Full Version : Sleeper: VBA hour calculation over 24h
Hello,
I try to add some hour:minutes (string formated) values in vba, but I doesn't work when I use strings like "101:10" (here I get an "type intolerance" error).
For example:
E9: "0:30"
E10: "15:45"
E11: "101:10"
With the local formula "=DATWERT(E9)+ZEITWERT(E9)+DATWERT(E11)+ZEITWERT(E11)+DATWERT(E12)+ZEITWERT( E12)" in H1 I get the right value "117:25" (format: [hh]:mm).
But my vba test-code:
Dim test As Range
For Each test In mylistobject.AutoFilter.Range.Columns(5).SpecialCells(xlCellTypeVisible).Ce lls.Offset(1, 0)
debug.print DateValue(test.Value2) + TimeValue(test.Value2)
Next test
throws an error when i access the E11 cell ("101:10"). It seems that DateValue/TimeValue works slighty different than the german functions.
I tried also DateValue(CDate(test.value2)) - but it doesn't worked too.
Any help is welcome ;)
Thank you,
Thomas
The only thing I can think of right now is
Format E9:E11 as "[h]:mm"
Debug.Print Format(test.Value2, "dd:hh:ss")
'Or (2 variations)
Debug.Print Format(test.Value, "[d]:hh:ss")
:dunno:
p45cal
10-26-2017, 08:27 AM
vba functions are frequently different from worksheet functions with the same name.
MOD is one example, I guess DateValue behave differently too.
The worksheet function DateValue seems to be happy dealing with hours >24, the vba function DateValue doesn't.
You could stick with the worksheet function by trying the likes of:
Evaluate("DateValue(""" & test.Value2 & """)")
or
yy = Evaluate("DateValue(""" & test & """)")
or longer:
yy = Evaluate("DateValue(""" & test & """) + TimeValue(""" & test & """)")
If the quotation marks are in the cell and show on the worksheet then perhaps:
yy = Evaluate("DateValue(" & test & ") + TimeValue(" & test & ")")
I'm not at all sure whether you'd have to:
yy = Evaluate("DATWERT(""" & test & """)")
WorksheetFunction.DateValue() ?
Paul_Hossler
10-26-2017, 07:16 PM
This is one ugly macro, but it does get the right answer
Option Explicit
Sub test()
Dim E9 As String, E10 As String, E11 As String
Dim X As Long
E9 = "0:30"
E10 = "15:45"
E11 = "101:10"
X = 60 * (1 * Split(E9, ":")(0) + 1 * Split(E10, ":")(0) + 1 * Split(E11, ":")(0)) + (1 * Split(E9, ":")(1) + 1 * Split(E10, ":")(1) + 1 * Split(E11, ":")(1))
MsgBox CLng(X / 60) & ":" & (X - 60 * CLng(X / 60))
End Sub
Maybe a function to take "mm:ss" or "mmm:ss" strings and convert to integer seconds to calculating, and then format as "mmm:ss" to display
p45cal
10-26-2017, 11:07 PM
WorksheetFunction.DateValue() ?Not available, at least not in Excel 2010 here, which is why I suggested Evaluate.
You can find if a worksheet function is available to vba by using the full Application.WorksheetFunction syntax when you'll get the intellisense.
I try to add some hour:minutes (string formated) values in vba, but I doesn't work when I use strings like "101:10" (here I get an "type intolerance" error).
Are the Cells considered as Dates or as Strings by Excel?
How to Test:
Strings. Select the Cell: If in the Formula Bar, the first character is an apostrophe ('), then it's definitely a String.
Dates: Format the cell as a Number with 10 decimal digits. If it then displays a decimal number, it's a Date.
Without that knowledge, You can't use code to manipulate them.
Generally, the Date/Time difference between two Date/Time values can be found as easily as
Date/Time1 - Date/Time2
The result will, of course, be in Decimal Days, which can be formatted with any Date/Time Formatting String.
MS Applications store and use Date/Time values as Double Type values, wherein the Date portion is the Integer pat of the value and the Time portion is the Decimal part.
The Date part of the Date/Time Value is merely the number of days since Jan 0, 1900. (Jan 1 = Date/Time Value 1.)
The Time part is stored as Decimal Days, ie 1/(24x60x60). 6 hours, 1/4 Day = .25/24 = 0.0104167 = 6AM
Today's date is stored as 43035.0, or 43035 days since Jan 0, 1900
At 7:24:59 AM here, Excel stores the time as 0.3090162037, or 0.3090162037 Days since midnight.
Paul_Hossler
10-27-2017, 06:41 AM
I thinking that the data is stored as strings, and not doubles formatted as "hhh:mm" or something
As SamT says, to do any calculations they have to numbers or something that Excel can convert to numbers
As something to think on, here's a user defined function that takes a variety of types and number of inputs and returns a string
20778
Option Explicit
Function SumTimeStrings(ParamArray A()) As Variant
Dim i As Long, j As Long
Dim X As Long
Dim v As Variant
On Error GoTo NiceExit
For i = LBound(A) To UBound(A)
If IsObject(A(i)) Then
If TypeName(A(i)) = "Range" Then
For j = 1 To A(i).Cells.Count
'note .Text and not value - make sure cell is not displayed as #########
' although .Value might work -- not tested
v = Split(A(i).Cells(j).Text, ":")
If UBound(v) = 1 Then
X = X + 60 * v(0) + 1 * v(1)
End If
Next j
End If
ElseIf VarType(A(i)) = vbString Then
v = Split(A(i), ":")
If UBound(v) = 1 Then
X = X + 60 * v(0) + 1 * v(1)
End If
End If
Next i
'note the \ for integer division
SumTimeStrings = CLng(X \ 60) & ":" & (X - 60 * CLng(X \ 60))
Exit Function
NiceExit:
SumTimeStrings = CVErr(xlErrNum)
End Function
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.