Results 1 to 8 of 8

(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. #1
    Board Regular
    Join Date
    May 2002
    Location
    Auckland
    Posts
    413

    Default (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. #2
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,663

    Default

    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. #3
    Board Regular
    Join Date
    Oct 2002
    Posts
    657

    Default

    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. #4
    Board Regular
    Join Date
    May 2002
    Location
    Auckland
    Posts
    413

    Default

    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. #5
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    17,043

    Default

    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"
    Office 2007/2010

  6. #6
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

    Default

    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. #7
    Board Regular
    Join Date
    May 2002
    Location
    Auckland
    Posts
    413

    Default

    To all who have offered help, thanks!

    MrExcel rocks!!!

  8. #8
    New Member
    Join Date
    Apr 2013
    Location
    Bangalore
    Posts
    6

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

    Formula is awesome

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com