Time Remaining

I have a situation where I have calculated the time elapsed between a start and finish time. What I am having trouble with is taking that result away from another field which represents hours purchased - to show the amount of time remaining. Any suggestions?
JoeBlowAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brewdogCommented:
How are you doing the calculation? Is it based on something like:

FinishTime - StartTime

where both times are stored in date/time fields? What about HoursPurchased? I'm guessing that is a Number field? Probably what you'd need to do is convert one of the values. Would something like this do the trick:

CLng(HoursPurchased) - (CLng(FinishTime-StartTime))

Hope this helps . . .

brewdog
0
BrianWrenCommented:
Proper time is stored as a decimal number where the whole number portion is the number of days since December 30, 1899, and teh decimal portion is then number of seconds since midnight.

If you take your time, (which I'm presuming is like "1:56" and convert it to a standard Widows time number, do your difference calculation, and then convert it back, using CDate().

By far the easiest thing to do would be to get the current time when you start, and store it in a variable.  Add the ETC, (Est Time of Compl.), to it, and store that.  Then in your calculation, get the current time, and subtract it from the ETC, and then convert the difference then post that in your remaining time display.

The reason for all of this is that it is a lot easier to do

  36550.8206134259 - 36550.8206133089
 
than
 
   12:51 - 11:38

Brian
0
paaskyCommented:
Hello JoeBlow,

If the difference between StartTime and FinishTime is less than 24 hours, you can use DateAdd -function:

HoursRemaining = Cint(Format(DateAdd("h", -1 * HoursPurchased, FinishTime),"h"))

Regards,
Paasky
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

paaskyCommented:
Uh.. forget my previous comment! What was I thinking??!

Paasky
0
paaskyCommented:
Here's a better solution:

Function HoursRemaining(HoursPurchased As Integer, StartTime As Date, FinishTime As Date)
    HoursRemaining = DateDiff("h", DateAdd("h", HoursPurchased, StartTime), FinishTime)
End Function

Regards,
Paasky
0
JoeBlowAuthor Commented:
The database stores the start and finish times as TimeIn and TimeOut.  The function I'm using to get the elapsed time is as follows:

Function GetElapsedTime(interval)
    Dim totalhours As Long, totalminutes As Long, totalseconds As Long
    Dim days As Long, hours As Long, minutes As Long, seconds As Long
    days = Int(CSng(interval))
    totalhours = Int(CSng(interval * 24))
    totalminutes = Int(CSng(interval * 1440))
    totalseconds = Int(CSng(interval * 86400))
    hours = totalhours Mod 24
    minutes = totalminutes Mod 60
    seconds = totalseconds Mod 60

GetElapsedTime = hours & " : " & minutes
End Function

The control is a text box using the following control source:
=GetElapsedTime([TimeOut]-[TimeIn])
0
BrianWrenCommented:
What is Int()?
0
brewdogCommented:
The integer value of what's inside the parentheses; same kind of idea as CInt. But CInt rounds to the nearest integer value, while Int just drops everything. (This is the way I understand it from the Help files.) So Int(4.7) becomes 4, while CInt(4.7) would become 5.
0
paaskyCommented:
BrianWren,

Almost like CInt()...

it was used with earlier Basic versions (GWBASIC, QuickBasic)

Paasky
0
paaskyCommented:
JoeBlow,

is this something you're looking for?

Public Function HoursRemaining(HoursPurchased As String, StartTime As Date, FinishTime As Date) As String
Dim Hours As Long
Dim Minutes As Integer
Dim TimeString As String
   
    Hours = CLng(Left(HoursPurchased, InStr(1, HoursPurchased, ":") - 1))
    Minutes = CInt(Mid(HoursPurchased, InStr(1, HoursPurchased, ":") + 1))
    TimeString = DateDiff("h", DateAdd("h", Hours, StartTime), FinishTime)
    TimeString = IIf(Minutes > 0, CStr(CLng(TimeString - 1)) & ":" & CStr(60 - Minutes), TimeString & ":00")
    HoursRemaining = TimeString
   
End Function

Regards,
Paasky
0
BrianWrenCommented:
What result are you getting vs what result do you want?

Brian
0
JoeBlowAuthor Commented:
For paasky

I gave you function a try, however I am getting a type mismatch error. In the debug window the line:

Minutes = CInt(Mid(HoursPurchased, InStr(1, HoursPurchased, ":") + 1))

is highlighted.


For BrianWren
The result I am getting is "#Error", the result I'm after is an expression of the result of time elapsed subtracted from time purchased and expressed as hrs : mins
0
JoeBlowAuthor Commented:
For Paasky

After some more thought I changed the data type of the hours purchased from Date/Time to a Number.

This cured the type mismatch error, however it now produces a "invalid procedure call or argument" error with the:

Hours = CLng(Left(HoursPurchased, InStr(1, HoursPurchased, ":") - 1))

line highlighted in the debug window.
0
paaskyCommented:
JoeBlow:

I'm sorry my poor documentation.

To use function HoursRemaining give the following parameters:

- HoursPurchased (string), this needs to be in string format "hh:mm" (hours:minutes)
- StartTime (date), starting date (and time) in default date format
- FinishTime (date), finishing date (and time)  in default date format

Function returns a string "hh:mm".

examples:

Me!Text1.Value = HoursRemaining("45:15","18.1.2000","28.1.2000")

or

Dim strHoursRemaining
strHoursRemaining = HoursRemaining("45:15","18.1.2000","28.1.2000")
Debug.Print strHoursRemaining

return value is "194:45" (194 hours and 45 minutes).


If you don't want to count minutes (or want to use only hours as parameters) you should change the function beginning lines a bit:

Public Function HoursRemaining(HoursPurchased As String, StartTime As Date, FinishTime As Date) As String
Dim Hours As Long
Dim Minutes As Integer
Dim TimeString As String
Dim P As Byte
   
    P = InStr(1, HoursPurchased, ":")
    If P > 0 Then
        Hours = CLng(Left(HoursPurchased, InStr(1, HoursPurchased, ":") - 1))
        Minutes = CInt(Mid(HoursPurchased, InStr(1, HoursPurchased, ":") + 1))
    Else
        Hours = CLng(HoursPurchased)
        Minutes = "00"
    End If
   
    TimeString = DateDiff("h", DateAdd("h", Hours, StartTime), FinishTime)
    TimeString = IIf(Minutes > 0, CStr(CLng(TimeString - 1)) & ":" & CStr(60 - Minutes), TimeString & ":00")
    HoursRemaining = TimeString
   
End Function

Regards,
Paasky


0
paaskyCommented:
JoeBlow,

Important note: my date is separated by period (.) because I'm using Finnish regional settings. You should use your own regional date format.

Paasky
0
paaskyCommented:
JoeBlow,

Few more examples (Debug Window testing):

? HoursRemaining("3:30","05:00","10:00")

1:30

? HoursRemaining("100:20","1.1.2000 05:00","15.1.2000 10:00")

240:40
0
JoeBlowAuthor Commented:
Paasky:

It would be great to be able to dispense with the minutes, however they are required for this Db.
I tried your function with the amendments, however I keep getting a negative number as the result.  For example in the debug window:

? hoursleft("15:00", "26.01.2000 17:30", "26.01.2000 19:00")

returns -13:00

0
paaskyCommented:
JoeBlow,

You get negative value because difference between starting time and finishing time is less than time purchased:
19:00 - 17:30 ~ 2:00 (1:30 is rounded to next full hour)

and when the time purchased is greater than the difference you get negative return value:
2:00 - 15:00 = -13:00

-13:00 could be read 13 hours after finishing time.

The way I thought it would go like

Starting time:   26.01.2000 12:00
Finishing time:  26.01.2000 17:00
Total time:      5:00
Hours purchased: 3:30
Hours left:      1:30

Tell me if you have thought this different way.

Regards,
Paasky


0
paaskyCommented:
JoeBlow,

In case exact minutes are needed use this function (same parameters as earlier version):

Public Function HoursRemaining(HoursPurchased As String, StartTime As Date, FinishTime As Date) As String
Dim Hours As Long
Dim Minutes As Long
Dim MinutesLeft As Long
Dim P As Byte
   
    ' Convert hh:mm string to hours and minutes
    P = InStr(1, HoursPurchased, ":")
    If P > 0 Then
        Hours = CLng(Left(HoursPurchased, InStr(1, HoursPurchased, ":") - 1))
        Minutes = CInt(Mid(HoursPurchased, InStr(1, HoursPurchased, ":") + 1))
    Else
        Hours = CLng(HoursPurchased)
        Minutes = 0
    End If
    Minutes = Hours * 60 + Minutes
   
    ' Calculate minutes left
    MinutesLeft = DateDiff("n", DateAdd("n", Minutes, StartTime), FinishTime)
   
    ' Convert minutes to hours and minutes
    Hours = Int(MinutesLeft / 60)
    MinutesLeft = MinutesLeft - Hours * 60
   
    ' Format the return value to hh:mm
    HoursRemaining = CStr(Format(Hours, "00")) & ":" & CStr(Format(MinutesLeft, "00"))
   
End Function


Debug testing:

? HoursRemaining("2:00","1.1.2000 1:45", "2.1.2000 1:32")
returns 21:47


Hope this helps,
Paasky
0
CEBAKACommented:
I would use the Windows Api:

Declare Function GetTickCount Lib "Kernel32" () As Long

Function GetTime() As Long
GetTime = GetTickCount
End Function

At the moment you start you start the function Gettime

lngStartTime=GetTickCount

When finished
lngEndTime=GetTickCount

Get the result
lngResult=lngEndTime-lngStartTime

Display time by
using the next function
Public Function CalcTime(dblTime) As String
Dim dblTempSeconds As Double
Dim dblTempMinutes As Double
Dim dblTempHours As Double

Dim strSecond As String
Dim strMinute As String
Dim strHour As String
Dim strTemp As String
Dim intX As Integer
If IsNull(dblTime) Then dblTime = 0
dblTempHours = dblTime / 3600
If dblTempHours < 1 Then dblTempHours = 0
If dblTempHours >= 1 Then dblTempHours = (dblTime - (dblTime Mod 3600)) / 3600

dblTempMinutes = dblTime - (dblTempHours * 3600)
If dblTempMinutes < 60 Then dblTempMinutes = 0
If dblTempMinutes > 59 Then dblTempMinutes = (dblTempMinutes - (dblTempMinutes Mod 60)) / 60
dblTempMinutes = CInt(dblTempMinutes)

dblTempSeconds = dblTime Mod 60

strSecond = IIf(dblTempSeconds < 10, "0" & dblTempSeconds, dblTempSeconds)
strMinute = IIf(dblTempMinutes < 10, "0" & dblTempMinutes, dblTempMinutes)
strHour = IIf(dblTempHours < 10, "0" & dblTempHours, dblTempHours)

CalcTime = "" & strHour & ":" & strMinute & ":" & strSecond & ""

End Function




PS
Be aware : store the time as LONG in the database. To display the time use the getTime function
0
CEBAKACommented:
I would use the Windows Api:

Declare Function GetTickCount Lib "Kernel32" () As Long

Function GetTime() As Long
GetTime = GetTickCount
End Function

At the moment you start you start the function Gettime

lngStartTime=GetTickCount

When finished
lngEndTime=GetTickCount

Get the result
lngResult=lngEndTime-lngStartTime

Display time by
using the next function
Public Function CalcTime(dblTime) As String
Dim dblTempSeconds As Double
Dim dblTempMinutes As Double
Dim dblTempHours As Double

Dim strSecond As String
Dim strMinute As String
Dim strHour As String
Dim strTemp As String
Dim intX As Integer
If IsNull(dblTime) Then dblTime = 0
dblTempHours = dblTime / 3600
If dblTempHours < 1 Then dblTempHours = 0
If dblTempHours >= 1 Then dblTempHours = (dblTime - (dblTime Mod 3600)) / 3600

dblTempMinutes = dblTime - (dblTempHours * 3600)
If dblTempMinutes < 60 Then dblTempMinutes = 0
If dblTempMinutes > 59 Then dblTempMinutes = (dblTempMinutes - (dblTempMinutes Mod 60)) / 60
dblTempMinutes = CInt(dblTempMinutes)

dblTempSeconds = dblTime Mod 60

strSecond = IIf(dblTempSeconds < 10, "0" & dblTempSeconds, dblTempSeconds)
strMinute = IIf(dblTempMinutes < 10, "0" & dblTempMinutes, dblTempMinutes)
strHour = IIf(dblTempHours < 10, "0" & dblTempHours, dblTempHours)

CalcTime = "" & strHour & ":" & strMinute & ":" & strSecond & ""

End Function




PS
Be aware : store the time as LONG in the database. To display the time use the getTime function
0
paaskyCommented:
Dear CEBAKA,

It is customary to not Answer a question unless you are 120% sure it will completely address the question. When the question is answered, it gets moved into the Locked Section, thus reducing the potential responses.

If you post your response as a comment, then the question gets more visibility in the Unanswered Section. If the asker feels that your comment is an appropriate response for their question they have the ability of accepting it as an answer and awarding you the points.

Regards,
Paasky
0
JoeBlowAuthor Commented:
This appears to be a "start timer, end timer, give elapsed time" type of function. What I'm actually after is a function that can take a start time and end time as stored in a database, calculate the elapsed time and subtract that time from an amount of hours purchased also stored in the database.
0
JoeBlowAuthor Commented:
For Paasky,

I really appreciate you efforts so far, your latest amendment is Soooooo close!
The amount of hours purchased is a starting amount from which the elapsed time has to be subtracted to give the time remaining as a positive amount if the time elapsed is less than the time purchased, for example:

Time Purchased: 10:00 hours
Start Time : 2/2/2000 16:00
End Time: 2/2/2000 17:35
Elapsed Time: 1:35
Time Remaining: 8:25
0
paaskyCommented:
JoeBlow,

Ah... I see. Whole time I thought Time remaining would be

TimeLeft = (FinishTime - StartTime) - TimePurchased

|--------+----------------|
S        P                F

S = Start Time
P = Time Purchased
F = Finish Time

And you want it this way

|----------+--------------|
S          F              P

TimeLeft = TimePurchased - (FinishTime-StartTime)

I make you new function after I get off work...

Regards,
Paasky
0
paaskyCommented:
(coffee break)

JoeBlow,

You just need to change one line in my code to get the result you wanted:

    ' Calculate minutes left
    MinutesLeft = Minutes - DateDiff("n", StartTime, FinishTime)

Paasky

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JoeBlowAuthor Commented:
For Paasky.

You are a winner. Thanks heaps and don't spend all the points at once.

JoeBlow
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.