DateDiff question. Multiply by

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.

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

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

the output is 1:30 so its not working

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

Select allOpen in new window

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

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.

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

I like simplicity and this was the simplest solution.  Thanks