Solved

How to calculate total hours and minutes between two time? (Urgent)

Posted on 2002-03-28
8
197 Views
Last Modified: 2010-05-02
In my form, it has two text box that i set its format to ##:## using MaskEdit.

These two textboxes are for user to key in time.
From 01:00  To 11:15

When user click the calculate button, program will calculate the time.  This is use to calculate total working hours.

The result.. should be 10:15, right?  how to do this?  any VB function can calculate this directly?

I used DateDiff function to calculate and return in minutes but the result not accurate. If not mistaken, it return 1 hours for this.

I have to save the calculation result in 10.15 (numberic field) so that i can use it to calculate total salary.  10.15 means 10 hours + 10 minutes.

I know the design is not good.. any good suggestion?
I have to save the total hours in numberic data type because in other forms, i used SQL to calculate total salary.  if save in string, the result will not accurate.

Please tell me how to solve this problem, thanks.
0
Comment
Question by:yongyih
8 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 6901964
Hi yongyih,

you can use datediff

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

HTH:O)Bruintje
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 6901966
Hi yongyih,

Try this:

Private Sub Command3_Click()
    Text1 = "13:00"
    Text2 = "23:15"
    Dim starttime As Date
    Dim stoptime As Date
    starttime = Date + CDate(Text1)
    stoptime = Date + CDate(Text2)
    MsgBox Format(stoptime - starttime, "hh:mm")
End Sub
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6901984
you used it already, i'm getting lazy

you have to format the result

Sub test()

Dim TimeOne As String
Dim TimeTwo As String

TimeOne = TimeValue(Now)
TimeTwo = TimeValue(NOw-.5)

MsgBox Format$(TimeValue(dteOut) - TimeValue(dteIn), "h:mm:ss")

end sub

HTH:O)Bruintje
0
 
LVL 44

Accepted Solution

by:
bruintje earned 20 total points
ID: 6901985
oops....

Sub test()

Dim TimeOne As String
Dim TimeTwo As String

TimeOne = TimeValue(Now)
TimeTwo = TimeValue(Now - 0.5)

MsgBox Format$(TimeValue(TimeOne) - TimeValue(TimeTwo), "h:mm:ss")

End Sub
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 2

Author Comment

by:yongyih
ID: 6902278
I try first, thanks.
0
 
LVL 4

Expert Comment

by:RichW
ID: 6902329
Try this:

Public Function TimeDiff(ByVal Interval As String, ByVal Time1 As String, ByVal Time2 As String)
   
    Dim HourTime1, HourTime2, MinuteTime1, MinuteTime2, SecondTime1, SecondTime2, TimeDifference
    Dim Time1InSeconds, Time2InSeconds
    Time1 = FormatDateTime(Time1, vbLongTime)
    Time2 = FormatDateTime(Time2, vbLongTime)
    HourTime1 = Hour(Time1)
    HourTime2 = Hour(Time2)
    MinuteTime1 = Minute(Time1)
    MinuteTime2 = Minute(Time2)
    SecondTime1 = Second(Time1)
    SecondTime2 = Second(Time2)
    Time1InSeconds = SecondTime1 + (MinuteTime1 * 60) + (HourTime1 * 3600)
    Time2InSeconds = SecondTime2 + (MinuteTime2 * 60) + (HourTime2 * 3600)
    TimeDifference = Time2InSeconds - Time1InSeconds
    Select Case Interval
        Case "h"
            TimeDifference = TimeDifference / 3600
        Case "m"
            TimeDifference = TimeDifference / 60
    End Select
    TimeDiff = TimeDifference
End Function

Call it this way,

TIME = TimeDiff("h", timStart, timNow)

Use "h" "m" "s" for hours minutes and seconds.

Good luck!
RichW
0
 
LVL 2

Author Comment

by:yongyih
ID: 6904704
Thanks everyone for your reply. ^_^
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6904738
u're welcome thanks for the points
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now