It's an extended question to this post. My question is a bit different, so I started a new thread.
It also may be more a math question than an Access one, but since I don't know the correct terminology, Google couldn't help.
I have a text box that shows a calculated elapsed time between two given date/time values.
example : 2 18:30:27 (2 days, 18 hours, 30 min & 27 sec)
How can I show this value as a 10 base number?
Some examples:
1 1:30:00 --> 25.5
0 1:15:00 --> 1.25
@plog I'm terribley sorry. I should have explained better. The elapsed time is a calculated time between two given date.
There are a lot of other intervals in between that effect the result. Machine preparations, trouble shootings, breaks, etc.
So I can not simply use Datediff between start & end time. I have to work on the return value of the function which is something like:
2 20:30:17
I don't understand your explanation. If you want to work with hours, then use date diff to give you a return in minutes. Then divide minutes by 60. That will give you a base 10 result in hours. Hours is the whole number and minutes is in fraction of an hour rather than minutes, so 45 minutes as the remainder = .75
Just to clarify for our members who didn't see (or didn't remember) the previous question to which KitaYama referred... the time in question is not the difference between two dates, but the sum of several smaller but non-contiguous (or not necessarily contiguous) intervals.
@Eugene-LS The result is not what I need. Per my example above,
1 1:30:00 --> 25.5 (1 day =24h then 24+1= 25 hours)
I didn't expect a user defined function to pass a date as string and split it and work on different parts seperately.
I hoped some vba internal functions can do the magic.
If it's the only way, I will work on user defined function to get the result.
Thanks for your time.
@Pat Hartman I'm not in a situation to explain it. I will get back to you as soon as I can.
Thanks for your help.
Just to clarify for our members who didn't see (or didn't remember) the previous question to which KitaYama referred... the time in question is not the difference between two dates, but the sum of several smaller but non-contiguous (or not necessarily contiguous) intervals.
Thanks Doc for the explanation. I specified the link to previous post in #1 to prevent any further confusion, but it's the holidays and everyone is busy with their family and kids. So there's a good chance they can not review previous posts.
I didn't expect a user defined function to pass a date as string and split it and work on different parts seperately.
I hoped some vba internal functions can do the magic.
If it's the only way, I will work on user defined function to get the result.
Here's an alternative approach, also using a function
Code:
Function ConvertBase10(DayTime As String) As Double
"calculate time in hours
Dim D As Integer, T As Date
'default values
D = 0
T = #12:00:00 AM#
'get the days
If InStr(DayTime, " ") > 0 Then
D = left(DayTime, InStr(DayTime, " "))
ElseIf InStr(DayTime, ":") = 0 Then
D = DayTime
End If
'get the time
If InStr(DayTime, ":") > 0 Then
T = Mid(DayTime, InStr(DayTime, " ") + 1)
End If
'do the math
ConvertBase10 = 24 * (D + CDbl(T))
End Function
As I said earlier, I was expecting an internal vba solution.
Now that I have to chop and butcher a time interval as a string to get what I want, I ended up using the following.
For now, the result is what I need.
Code:
Public Function TimeTo10Base(V) As Currency
Dim t As Date
Dim d As Single
If V & "" = "" Then Exit Function
TimeTo10Base = Split(V, " ")(0) * 24
t = Split(V, " ")(1)
d = DateDiff("s", TimeSerial(0, 0, 0), t) / 3600
TimeTo10Base = TimeTo10Base + Round(d, 2)
End Function
Thanks to all who shared their time, wisdom and experience here.
I really appreciate every given advice.
1) A DateTime value is managed internally as a number of type Double. The integers are the days since Dec 30, 1899, the decimal parts are fractions of a day (1 hour = 1/24, 1 minute = 1/1440, 1second = 1/86400).
Sample calculations
Since you have a number with a DateTime value, you can calculate directly mathematically, e.g. adding or subtracting or summing.
2) Now comes the big game between value and display of value (view). Nobody can do anything with the double number, so it is presented as a country-specific format for the date and time. So you see a format of the intrinsic value and not the value itself. You can also display a date as a calendar week or quarter or day of the year. There are a few options.
When it comes to formatting, you have to make a big distinction between the format property and the format function. The format property (e.g. for a text field of a form) only changes the display but not the inner value, it remains a number. The format function necessarily creates a string and therefore something else. A string is different from a number and therefore has a different use.
3) For times over 24 hours there is no format that can be used in the property. So you have to switch to the function.
In practical use, it makes little sense to always have to convert between numbers and strings; the computer's processor should deal with important things. It will therefore often make sense to run both the number (for subsequent calculations) and the string (for a view, if required) in parallel in one query for bulk data processing.
4) A DateTime value as a double usually has sufficient accuracy, but double is a floating-point number and therefore has system-related inaccuracies. Time1 = Time2 may be wrong, although the formats shown are identical, since these times could be generated differently and differ in some trailing decimal place. So be careful there.
If you have higher requirements for accuracy, you can have your DateTime value managed over the seconds. For example, the UNIX timestamp counts the seconds since Jan 1 1970. Using DateAdd and DateDiff you can easily convert to the usual form. With the used seconds basis one also gets a practical access to fractions of seconds (milli-/nanoseconds).
@ebs17 Thanks for the detailed and additional explanation. But my main problem is I have to work on interval of time (or elapsed time). Not on date or time itself.
The result of EndingTime - StartingTime is not a date or a time. It's a number that can be in seconds or hours or days.
You have your summed time from your other thread - assuming this is numeric then just multiply by 24 (86400/3600 - number of seconds in a day / number of seconds in an hour)
@CJ_London I really don't know how to thank you.
Since (per given advice) I had changed the data type of the summed variable to Date, I didn't noticed this solution.
You're really a life saver.
Now I can sleep in peace. No more additional functions.
The first expression shows the resulting double number, which would have to be converted into an understandable format by formatting or conversion. The second expression calculates in seconds.
If you had just used seconds in your original calculation, it would be simpler still.
For example, a total of 1 day 1 hour and 15 minutes = 86400+3600+900 = 90900 s
If you had just used seconds in your original calculation, it would be simpler still.
For example, a total of 1 day 1 hour and 15 minutes = 86400+3600+900 = 90900 s
