(VBA) Calculate the difference between two times - 24hr

[jag108]

Hi,

I have a user form with multiple text boxes on it one called start time, and one called finish time.

What I need to be able to do is get the hour(s) betwen the two, the trick is that the start time may be 20:00 and the finish time may be 06:00, so a 24 hour contingency plan is required.

i.e. 20:00 - 06:00 = 10hrs
the same will go for 08:00 - 18:00 = 10hrs


I am trying to calculate a time sheet using VBA instead of formulas, because the formulas are way to complicated to get right so VBA is having t be the way.

 

[pgc01]

Hi jag108

A formula solution.

start time in A1
finish time in B1

both as excel time.

The total hours between the two:

=B1-A1+(A1>B1)

formatted as hh:mm

Hope this helps
PGC

 

[Chas17]

Try the DateDiff VBA function

DateDiff Function Example
This example uses the DateDiff function to display the number of days between a given date and today.

Dim TheDate As Date ' Declare variables.
Dim Msg
TheDate = InputBox("Enter a date")
Msg = "Days from today: " & DateDiff("d", Now, TheDate)
MsgBox Msg

Chas

 

[jag108]

Thanks Guys.

but I need to be able to take

start_date = textbox1.text
end_date - textbox2.text

then do the calculation.
I have tried DateDiff could not get it to work with 24 hour values where start_date looks like it is greater the the end_date.

20:00 // 06:00 makes DateDiff spit the dummy, I start getting negative numbers.

 

[Scott Huish]

Perhaps something like this:

start_date = TimeValue(TextBox1)
end_date = TimeValue(TextBox2)
MsgBox Abs((end_date - start_date) - (start_date > end_date)) * 24 & " hrs"

 

[jindon]

You need to convert string in textbox to Datevalue like

dim start_date As Date, end_date As date
start_date = DateValue(textbox1.text)
end_date = DateValue(txtbox2.text)

OOps! it was a TimeValue...

 

[jag108]

To all who have offered help, thanks!

MrExcel rocks!!!

 

[latifpatel]

Formula is awesome

 

[TAPS_MikeDion]

Wow, Scotts reply was 13 years ago (on my B-Day too LOL) and that reply just helped me with my current VBA issue. Thanks Scott!