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.