Solved

# Time Remaining

Posted on 2000-01-25
Medium Priority
341 Views
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?
0
Question by:JoeBlow
• 13
• 7
• 3
• +2

LVL 10

Expert Comment

ID: 2387455
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

LVL 9

Expert Comment

ID: 2387486
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

LVL 10

Expert Comment

ID: 2387694
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,
0

LVL 10

Expert Comment

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

0

LVL 10

Expert Comment

ID: 2387737
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,
0

Author Comment

ID: 2390478
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

LVL 9

Expert Comment

ID: 2390500
What is Int()?
0

LVL 10

Expert Comment

ID: 2390518
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

LVL 10

Expert Comment

ID: 2390520
BrianWren,

Almost like CInt()...

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

0

LVL 10

Expert Comment

ID: 2390586
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,
0

LVL 9

Expert Comment

ID: 2390601
What result are you getting vs what result do you want?

Brian
0

Author Comment

ID: 2390853

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

Author Comment

ID: 2390921

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

LVL 10

Expert Comment

ID: 2391817
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,

0

LVL 10

Expert Comment

ID: 2391819
JoeBlow,

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

0

LVL 10

Expert Comment

ID: 2391826
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

Author Comment

ID: 2394948

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

LVL 10

Expert Comment

ID: 2395747
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,

0

LVL 10

Expert Comment

ID: 2416101
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,
0

LVL 1

Expert Comment

ID: 2446681
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

LVL 1

Expert Comment

ID: 2446721
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

LVL 10

Expert Comment

ID: 2464223
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,
0

Author Comment

ID: 2476174
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

Author Comment

ID: 2476195

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

LVL 10

Expert Comment

ID: 2477258
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,
0

LVL 10

Accepted Solution

ID: 2477314
(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)

0

Author Comment

ID: 2480642

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

JoeBlow
0

## Featured Post

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…
###### Suggested Courses
Course of the Month6 days, 7 hours left to enroll