DateDiff question.  Multiply by

al4629740
al4629740 used Ask the Experts™
on
How do I multiply a time value by a whole number in VB6 using the DateDiff function


For example:  If I multiply 1:30 by 10, then my output should be 10:30 hours.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
Can you clarify the rules for your multiplication? It seems as though you are multiplying the hours portion by the multiplier. If that is the case, then what do you do when it rolls over? In other words, given 3:00 with a multiplier of 10, the result is 30:00. Is that 1 day and 6 hours, or is it simply 6 hours?

Also, why would you use DateDiff? That function is intended to give you the amount of time between two dates. I would think that DatePart would be more useful.

Author

Commented:
Yes, that would be more useful.  In your example I would be multiplying 3:00 hours by 10.  It simply needs to be in a date format

Commented:
Private Sub Form_Load()
a = "1:30"
Multiplier = 2
result = myFunc(a, Multiplier)
MsgBox result
End Sub

Function myFunc(ByVal L2 As Date, ByVal Multiplier As Long)
ha = Hour(L2)
M = ha * Multiplier & ":" & Minute(L2)
myFunc = M
End Function

Author

Commented:
the output is 1:30 so its not working
GrahamSkanRetired
Top Expert 2012

Commented:
It's better to declare variables as the appropriate types
Private Sub Form_Load()
    Dim a As Date
    Dim Multiplier As Long
    Dim Result As Date
    
    a = CDate("1:30")
    Multiplier = 2
    Result = Multiplier * a
    MsgBox Result
End Sub

Open in new window

Commented:
al4629740 wrote:
>>the output is 1:30 so its not working

If you have dimensioned the variables correctly, the output is "2:30"
and not "1:30" :


Option Explicit
Private Sub Form_Load()
Dim a As String, Multiplier As Integer, result As String
a = "1:30"
Multiplier = 2
result = myFunc(a, Multiplier)
MsgBox result
End Sub

Function myFunc(ByVal L2 As Date, ByVal Multiplier As Long)
Dim m As String, ha As Integer
ha = Hour(L2)
m = ha * Multiplier & ":" & Minute(L2)
myFunc = m
End Function

Author

Commented:
GrahamSkan

The output is .125

So that I make more sense, let me explain what I am exactly looking for.

The first variable is the amount of HOURS listed in a Time format.

I need to multiply the number of days(whole number) by the hours to get a TOTAL amount of hours

The final result needs to be in a Time format also.
Retired
Top Expert 2012
Commented:
Using the test figures in my snippet, the output is "03:00:00". That is the same as  .125


You can specify the format to display in the way that you want it:
    Dim a As Date
    Dim Multiplier As Long
    Dim Result As Date
    
    a = CDate("1:30")
    Multiplier = 2
    Result = Multiplier * a
    MsgBox Format(Result, "hh:nn")

Open in new window

Commented:
al4629740 wrote:
>>The final result needs to be in a Time format also.
Try this :

Enum ole2
    outputAsHours = 1
    outputAsDays = 2
    outputAsDayTimeFormat = 3
End Enum

Option Explicit

Private Sub Form_Load()
Dim a As String, Multiplier As Integer, result As String
a = "1:31"
Multiplier = 20

result = myFunc(a, outputAsHours, Multiplier)
MsgBox result

result = myFunc(a, outputAsDays, Multiplier)
MsgBox result

result = myFunc(a, outputAsDayTimeFormat, Multiplier)
MsgBox result

End Sub

Function myFunc(ByVal L2 As Date, ByVal L3 As ole2, ByVal Multiplier As Long)
Dim ha As Integer, hb As Integer, hc As Single

ha = Hour(L2): hb = Minute(L2)
hc = (ha * 60) + hb 'how many minutes is it?
hc = hc * Multiplier 'multiply it

Select Case L3
    Case 1
    hc = Round(hc / 60, 2) 'convert minutes to hours
    myFunc = hc & " hours"
   
    Case 2
    hc = Round(hc / 60 / 24, 3) 'convert minutes to days
    myFunc = hc & " days"
   
    Case 3
    hc = (hc / 60 / 24) 'convert minutes to days
    myFunc = Fix(hc)  'convert days to "common time format"
    myFunc = myFunc & " days and "
    myFunc = myFunc & Format(hc, "hh:nn")
    myFunc = myFunc & "  hh:min "
End Select

End Function

Author

Commented:
I like simplicity and this was the simplest solution.  Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial