Date function caused error 13 type mismatch 1

[vba317]

I am trying to subtract two times from each other with no luck. I have tried a couple of combinations with no luck. I am hoping to get some help. Here is my code.

Dim myNum6 As Date
Dim myNum7 As Date

myNum6 = Application.InputBox("Enter Start Time")
myNum7 = Application.InputBox("Enter End Time")

'Start Time
Range("J" & LR - 1).Value = myNum6

'End Time
Range("K" & LR - 1).Value = myNum7

Range("M" & LR).Formula = "=" & (("J" & LR - 1) * 24) - Range(("K" & LR - 1) * 24)

Range("M" & LR).Select
With Selection
       .NumberFormat = "0.00"
   End With

 

[DaveInIowa]

How about something like this?

Range("M" & LR).Formula = Replace("=DurationInHours(J<row>,K<row>)", "<row>", CStr(LR - 1))

...

Public Function DurationInHours(time1 As Date, time2 As Date) As Integer
    DurationInHours = DateDiff("h", time1, time2)
End Function

 

[fumei]

Perhaps more to the point, how are you ensuring that whatever is entered into the InputBox is, in fact, a date? Error 13 is a mis-match error.

It looks to me that whatever is returned by the InputBox is NOT a date.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[vba317]

Gerry,
I am entering in time not the date. I would like to have error checking capability but I don't know how to do that.

Dave in Iowa I don't think this solution will work because I want the formula to be entered into the Range("M" & LR).Select Cell not the calculated value. Currently the formula that is on my spreadsheet is =((K8024)*24)-(J8024)*24

 

[DaveInIowa]

I want the formula to be entered ... not the calculated value

That's what the example I provided does.

 

[DaveInIowa]

Here's an example incorporating error checking and eliminating the user-defined-function. I'm assuming the variable LR is globally available:

Public Sub YourSub()
    Dim jDate As Date
    Dim kDate As Date
    Dim inputBoxTime As String
    
    inputBoxTime = GetTimeAsString("Enter Start Time:")
    If (inputBoxTime = "") Then Exit Sub
    jDate = CDate(inputBoxTime)
    
    inputBoxTime = GetTimeAsString("Enter End Time:")
    If (inputBoxTime = "") Then Exit Sub
    kDate = CDate(inputBoxTime)
    
    ' Start time
    Range("J" & CStr(LR - 1)).Value = jDate
    
    ' End time
    Range("K" & CStr(LR - 1)).Value = kDate
    
    ' Duration
    With Range("M" & CStr(LR))
        .Formula = Replace("= (Kr - Jr) * 24", "r", CStr(LR - 1))
        .NumberFormat = "0.00"
    End With

End Sub

Public Function GetTimeAsString(defaultPrompt As String) As String
    Dim time As String
    Dim prompt As String
    
    prompt = vbCrLf & vbCrLf & defaultPrompt
    
    Do While (prompt > "")
        time = Trim(InputBox(prompt, "Get Time", time))
        
        If (time = "") Then
            prompt = ""
        ElseIf (IsDate(time)) Then
            GetTimeAsString = time
            prompt = ""
        Else
            prompt = "Invalid time entered." & vbCrLf & vbCrLf & defaultPrompt
        End If
        
    Loop
        
End Function

 

[vba317]

This subroutine worked great! I will humbly admit that this newbie will have to disect the lines so I can understand everything that is going on.

Currently if I enter a time in the afternoon between 1 and 5 (my working hours) I have to enter 13:00 to 17:00. Is it possible to add something to the function block so that when I enter a time between 1 and 5 it calculates it as PM instead of AM?

If not I really appreciate the help you have already give me.

 

[DaveInIowa]

Just append a "p" to your time value entry; VBA will figure it out for you. For example "1p", "2pm" and "3:15p" will be correctly interpreted as 1:00 PM, 2:00 PM and 3:15 PM respectively (you will need to enter the colon to signify any minutes other than zero).

Also, adding the following additional code to the GetTimeAsString function will catch any date values mistakenly entered:

...
        ElseIf (IsDate(time)) Then
            [highlight]If (CDate(time) >= #1/1/1900#) Then[/highlight]
                [highlight]prompt = "Only time values may be entered." & vbCrLf & vbCrLf & defaultPrompt[/highlight]
            [highlight]Else[/highlight]
                GetTimeAsString = time
                prompt = ""
            [highlight]End If[/highlight]
        Else
...