PDA

View Full Version : Sleeper: VBA hour calculation over 24h



aua
10-26-2017, 02:27 AM
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

SamT
10-26-2017, 07:50 AM
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 & """)")

SamT
10-26-2017, 05:03 PM
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.

SamT
10-27-2017, 05:28 AM
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