Calculating the difference between 2 time values using VBA

[Denny57]

I have a couple of requests concerning two Time formatted Text Boxes

Both records are converted from text to the format hh:mm (see Below) when written to the worksheet as part of a command module

If AddRecord Then CurrentRow = wsDailyHours.Range("A" & wsDailyHours.Rows.Count).End(xlUp).Row + 1
determines the next available row in the worksheet (wsDailyHours)

The following lines are extracts from the Command Code and which currently write the hh:mm values to Columns E and F in the worksheet
.Cells(CurrentRow, 5).Value = CDate(Format(txtStartTime.Text, "hh:mm"))
.Cells(CurrentRow, 6).Value = CDate(Format(txtFinishTime.Text, "hh:mm"))

I am looking to create code that will firstly calculate the difference between txtFinishTime and txtStartTime in format hh:mm. This value would then be added to column G in the worksheet [.Cells(Current Row, 7).Value = .....]

Next I would like to convert the resulting value of Column G to a two decimal place value in Column H again as the record is written to the worksheet

I wish to maintain the hh:mm format in Column G

Hoping someone can assist

 

[Micron]

Does this syntax work for the math part?
application.text((timevalue(#09:14#) - timevalue(#08:14#)),"[h]:mm")

I get one hour (1:00) as an answer, but that is the limit of my testing so far:

convert the resulting value of Column G to a two decimal place value in Column H

No idea what you want that to look like since doing math with date/time values results in a portion of a day. If you're going to first represent that as hh:mm what you have there is a string value. The true value of

(timevalue(#09:14#) - timevalue(#08:14#))

is 4.16666666666667E-02, which represents decimal portion of a day (but in scientific notation). If you times that by 24 because of 24 hours in a day, you get 1 (one hour). Maybe you'd need to format, as in

format((timevalue(#09:14#) - timevalue(#08:14#))*24,".00")
which gives me 1 with 2 decimal places:

1.00

 

[Denny57]

Does this syntax work for the math part?
application.text((timevalue(#09:14#) - timevalue(#08:14#)),"[h]:mm")

I get one hour (1:00) as an answer, but that is the limit of my testing so far:

No idea what you want that to look like since doing math with date/time values results in a portion of a day. If you're going to first represent that as hh:mm what you have there is a string value. The true value of

(timevalue(#09:14#) - timevalue(#08:14#))

is 4.16666666666667E-02, which represents decimal portion of a day (but in scientific notation). If you times that by 24 because of 24 hours in a day, you get 1 (one hour). Maybe you'd need to format, as in

format((timevalue(#09:14#) - timevalue(#08:14#))*24,".00")
which gives me 1 with 2 decimal places:

1.00

Micron

I have created the additional lines in the command module

.Cells(CurrentRow, 5).Value = CDate(Format(txtStartTime.Text, "hh:mm"))
.Cells(CurrentRow, 6).Value = CDate(Format(txtFinishTime.Text, "hh:mm"))
.Cells(CurrentRow, 7).Value = Application.Text((txtFinishTime.Value - txtStartTime.Value), "hh:mm")
.Cells(CurrentRow, 8).Value = Format((txtFinishTime.Value - txtStartTime.Value) * 24, ".00")

This works fine when I add new records, however I gat a warming Message Box "Type Mismatch" but I do not think that this relates to this coding.

Many thanks

 

[Micron]

Did you step through the code line by line to see which one raised that error?
This
Format((txtFinishTime.Value - txtStartTime.Value)
is not what I wrote
format((timevalue(#09:14#) - timevalue(#08:14#))*24,".00")

TimeValue is a function that you omitted. I guess you ought to have format((timevalue(txtFinishTime) - timevalue(txtStartTime))*24,".00") - BUT that likely depends on the data type contained in those controls.

BTW, you almost never need to refer to .Value. txtFinishTime alone would work because .Value is the default property of most controls (as well as cell and range if I'm not mistaken). However, it doesn't hurt to use it.

 

[Denny57]

Hi Micron

I have only just found time to review the code changes and I believe I have found where the "Type Mismatch" is happening, as not all data from the Input code is writing to the worksheet.

Input Command

Private Sub cmdInputRecords_Click()
    Dim answer      As VbMsgBoxResult
    Dim AddRecord   As Boolean
  
    AddRecord = Val(Me.cmdInputRecords.Tag) = xlAdd
  
    answer = MsgBox(IIf(AddRecord, "Add New", "Update Current") & " Record?", 36, "Information")
    If answer = vbYes Then
    

 
        'new record
        If AddRecord Then CurrentRow = wsDailyHours.Range("A" & wsDailyHours.Rows.Count).End(xlUp).Row + 1
      
        On Error GoTo myerror
        With wsDailyHours
            .[B]Cells(CurrentRow, 1).Value = CDate(txtWorkLeaveDate.Value)
            .Cells(CurrentRow, 2).Value = cboSchedulingType.Value
            .Cells(CurrentRow, 3).Value = cboLocation.Value
            .Cells(CurrentRow, 4).Value = txtPayMonthInput.Value[/B]
            '.Cells(CurrentRow, 5).Value = txtStartTime.Value
            '.Cells(CurrentRow, 6).Value = txtFinishTime.Value
            .[B]Cells(CurrentRow, 5).Value = CDate(Format(txtStartTime.Text, "hh:mm"))
            .Cells(CurrentRow, 6).Value = CDate(Format(txtFinishTime.Text, "hh:mm"))[/B]
            .Cells(CurrentRow, 7).Value = Application.Text((txtFinishTime.Value - txtStartTime.Value), "hh:mm")
            .Cells(CurrentRow, 8).Value = Format((txtFinishTime.Value - txtStartTime.Value) * 24, ".00")
            [I]'.Cells(CurrentRow, 9).Value = To be a VLookup of the value of CurrentRow, 8
            '.Cells(CurrentRow, 10).Value To be the result of CurrentRow, 8 less the value of the Looked up value in CurrentRow, 9[/I]
            .Cells(CurrentRow, 11).Value = cboPayRate.Value
            .Cells(CurrentRow, 13).Value = CheckBoxPete.Value
            .Cells(CurrentRow, 14).Value = CheckBoxKirsty.Value
            .Cells(CurrentRow, 15).Value = CheckBoxJan.Value
            .Cells(CurrentRow, 16).Value = CheckBoxKelly.Value
            .Cells(CurrentRow, 17).Value = CheckBoxCarla.Value
        End With
      
        MsgBox "Record has been " & IIf(AddRecord, "added", "updated") & " to the database", 64, "Information"
      
    End If
    
    With ActiveSheet
    Application.Goto Reference:=.Cells(.Rows.Count, "A").End(xlUp).Offset(-15), Scroll:=True
    End With

    Call cmdClearForm_Click
    txtWorkLeaveDate.SetFocus
  
myerror:
 If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"

'With Me
'.txtPayMonthInput = Application.WorksheetFunction.VLookup(txtWorkLeaveDate, Sheet2.Range("A4:G372"), 2, False)
'End With
End Sub

The code in BOLD are those that are copying across ok

I also now understand that timevalue is a function, In the examples you tested, the times were quoted as #09:14# and #08:14# whereas these details will be the value of txtStartTime and txtFinishTime.

Any suggestions you can give me to resolve this problem would be most welcome

 

[Micron]

VBA code tags removes html codes but I think I can spot your bold parts. You need to state in a post which line causes the error or identify it in your code with comments. If it doesn't break and then highlight the offending line then you need to step through your code as I noted, so that you can identify the problem line(s).

This looks suspect
AddRecord = Val(Me.cmdInputRecords.Tag)=xlAdd
Tag property holds a string so what is your tag property value? I also wonder why you're using Val on it. Without bracketing you have no control over the order of logical operations like that. Here's some guesses as to how that might play out if .Tag property is "123Dog":

Val returns "123" and you try to pass it to Addrecord, which is a Boolean, which can accept 123 (anything other than 0 or -1 will be True)
or Val returns 0 (because "Dog" is the Tag property value) and 0 can also be Boolean. Either way, the result could be
(AddRecord = Val(Me.cmdInputRecords.Tag))=xlAdd
or
AddRecord = (Val(Me.cmdInputRecords.Tag)=xlAdd)
see the difference?

Another potential problem is xlAdd. It's undeclared so I guess you're not using Option Explicit in your code modules (or it's declared somewhere else as a global variable). So if not declared I expect it to be a variant variable, in which case it may be the issue since it may hold an empty string ( "" ) and you're trying to use it along with a Boolean.

If none of that helps, consider posting a copy of the wb (with sensitive data removed) on a file share and post a link to it. Just make sure the copy exhibits the problem if you do.

 

[Denny57]

Good Evening Micron

I have shared the file which I am using to try to perform code changes to replace the formula which are "pre-loaded" into some cells in The "Daily Hours Input" Worksheet

I will agree that the code is probably not best formatted however, I have used commands and functions which have worked for me in other files / projects. However, is appears there are many ways that VBA can be written and so much of my coding will be a hybrid which may be unsuitable.

Ideally I am looking to input details from the User Form as follows

Resolved Fields from User Form
Work Leave Date - Column A (As UK dd/mm/yyyy format)
Pay Month - Column B (As Look up)
Scheduling Type - Column C
Location - Column D
Start Time - Column E (As hh:mm format)
Finish Time - Column F (As hh:mm Format)
Pay Rate - Column K

Data Requiring Coding at Input / Update (Also as comments in the Form code"
Column G - Column F less Column E (As hh:mm format) {Might seem redundant but required for later use}
Column H - Column G (as a decimal)
Column I - VLookup on Worksheet "Break Allowance & Calculator"
Column J - Column H less Column I
Column L - Column J multiplied by Column K (As currency)

I am looking to delete records ad hoc which I will be able to code but I also wish to sort all populated rows in Date Order (Column A) using either a "cmdSort" command button which I will add or preferably happen as records are added / updated.

I have provided a link to the entire file.

Hoping you can give me some pointers.

Dave

Dropbox

www.dropbox.com

 

[Micron]

As soon as it reached this line it raised an error. Note the bubble showing the value of txtWorkLeaveDate.

You cannot convert an empty string to a date (and probably no other conversion function would work either). What I'm seeing is that if the form has no values the controls are empty yet that block of code still executes. Are you raising this error with an empty form as well, or are you doing something else? You'll need to explain how to replicate the error the same way you are if your form has data in the controls.

Perhaps what you should be doing is validating control values that are involved in the conversion functions to ensure they have data of the correct type. F'rinstance, if a date control contains "dog" or a single space, your code will still fail when it tries to convert this to a date even though it's not empty. Did you step through the code and check your variable values as I suggested? Don't think so, otherwise you would have posted which line was raising the error.

 

[Micron]

Next time you have this sort of thing

    txtWorkLeaveDate.Value = ""
    txtPayMonthInput.Value = ""
    cboSchedulingType.Value = ""
    cboLocation.Value = ""
    txtStartTime.Value = "00:00"
    txtStartTime.MaxLength = 5
    txtFinishTime.Value = "00:00"
    txtFinishTime.MaxLength = 5
    cboPayRate.Value = ""
    CheckBoxPete.Value = False
    CheckBoxKirsty.Value = False
    CheckBoxJan.Value = False
    CheckBoxKelly.Value = False
    CheckBoxCarla.Value = False
    txtWorkDate.Value = ""
    txtDailyPayMonth.Value = ""
    txtDailyWorkHours.Value = ""
    txtDailyLeaveHours.Value = ""
    txtDailyNonWorkingDay.Value = ""
    txtDailyEarningsGross.Value = ""
    txtMonthlyWorkHours.Value = ""
    txtDailyEarningsGross.Value = ""
    txtMonthlyPayMonth.Value = ""
    txtMonthlyWorkHours.Value = ""
    txtMonthlyWorkEarningsGross.Value = ""
    txtMonthlyLeaveHours.Value = ""
    txtMonthlyLeaveEarningsGross.Value = ""
    txtTotalMonthlyEarningsGross.Value = ""
    txtDateSearch.Value = ""
    cboPayMonthSearch.Value = ""
  
    txtWorkLeaveDate.SetFocus

you can do this sort of thing instead

Dim ctl As Control

For Each ctl In Me.Controls
    If TypeName.ctl = "Textbox" Or TypeName = "Combobox" Then ctl = ""
    If TypeName.ctl = "Checkbox" Then ctl = False
Next

txtStartTime = "00:00"
txtStartTime.MaxLength = 5

txtWorkLeaveDate.SetFocus

You'll have to make allowances if that isn't quite right because it's different in Excel than it is in Access, and I know the latter one better.