CODEWITHSUNDEEP
vbaexcel
Forward Ed
Forward Ed

Reputation: 9874

HOUR Function works as a formula but not as VBA code for 24:00

So I have a procedure that I thought was working last time I checked it but apparently it is not. The problem I am having is with 24:00 and the hour check. Yes a classic problem apparently. When entering 24:00 and I use =hour(A1) where A1 has 24:00 in it, I get the answer of 0, and that part I am ok with. I even have some other code that says if timehour = 0 then timehour = 24 and I carry on from there and all is peachy.

Today when I went to enter my 24:00 and used the hour function in VBA it returned run-time error '13': Type mismatch instead of 0.

SET UP

Cell E4 and E5 in excel has validated data list for the user to pick from in a drop down that ranges from 01:00, 02:00, 03:00, ..., 24:00. All formatted as text. If the user has selected 12 hour clock in set up then the times are listed 1:00 AM, 2:00 AM, 3:00 AM, ..., 11:00 PM, 12:00 AM.

CODE

Private Sub Set_Work_Hours()
    Dim setStartHour As Integer
    Dim setEndHour As Integer
    Dim sht As Worksheet
    
    'setting the worksheet to be working with
    Set sht = ThisWorkbook.Worksheets("Daily")
    sht.Activate
    
    'hardcode location of start and end times from daily sheet
    setStartHour = Hour(Cells(4, 5).Value)
    setEndHour = Hour(Cells(5, 5).Value)
        
    Call Display_Work_Hours(setStartHour, setEndHour)
    
End Sub

I get the error at setStartHour or setEndHour when I select 24:00 from the list. In another cell in the spreadsheet, I had the formula =HOUR(E4) and =HOUR(E5) and they both return 0 when 24:00 is selected.

Why is the VBA version of HOUR not returning zero?

Clarification on the question. Why am I getting two different results from excel formula to VBA function?

My work around

Private Sub Set_Work_Hours()
    Dim setStartHour As Integer
    Dim setEndHour As Integer
    Dim sht As Worksheet
    
    'setting the worksheet to be working with
    Set sht = ThisWorkbook.Worksheets("Daily")
    sht.Activate
    
    'hardcode location of start and end times from daily sheet
    If Cells(4, 5).Value = "24:00" Then
        setStartHour = 0
    Else
        setStartHour = Hour(Cells(4, 5).Value)
    End If
    If Cells(5, 5).Value = "24:00" Then
        setEndHour = 0
    Else
        setEndHour = Hour(Cells(5, 5).Value)
    End If
    
    Call Display_Work_Hours(setStartHour, setEndHour)
    
End Sub

Upvotes: 1

Views: 1521

Answers (2)

grug.0
grug.0

Reputation: 355

As a general suggestion, why don't you just run all of your DateTime variables in UNIX time on the backend, and just simply convert to a user-readable date on the spreadsheet?

This way no errors!!

Convert from human readable date to epoch 

DateDiff("s", "01/01/1970 00:00:00", time field)

How to get the current epoch time 

DateDiff("s", "01/01/1970 00:00:00", Now())

Upvotes: 0

Kyle
Kyle

Reputation: 564

Just expanding upon my comments. Excel stores dates and times as a serial number. The integer portion of the serial number is the number of days since 1/1/1900, and the decimal portion is the hours.

When you use hours as a string (e.g. "24:00"), Excel converts it to a serial number, in this case 1.0, or 1/1/1900 0:00. VBA does not implicitly convert invalid times like this, which is why you were seeing an error. The correct way to represent "24:00" in VBA is "00:00".

Upvotes: 1

Related Questions