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

This is a discussion on (VBA) Calculate the difference between two times - 24hr within the Excel Questions forums, part of the Question Forums category; Hi, I have a user form with multiple text boxes on it one called start time, and one called finish ...

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

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.

2. 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

3. 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

4. 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.

5. Perhaps something like this:

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

6. 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...

7. To all who have offered help, thanks!

MrExcel rocks!!!

8. ## Re: (VBA) Calculate the difference between two times - 24hr

Formula is awesome

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•