Convert short time to a decimal.

[allong]

I have a report with a start time and an endtime. In order to find the different past midnight I put in
format([starttime]-1 -[endtine], "short time" this give be the hours i need except it is in short time form (3:30). I tried using timetodec(Mytime) and I get #error. Is there any other way to convert the short time to a decimal? I want to be able to sum of the decimal column.

 

[scriverb]

As a date field is stored as a floating point number(Dbl) the time is stored as the decimal portion of the value. The following gives you the decimal portion of the date/time field:

CDbl(<DateField>) - (CLng(CDbl(<DateField>) - 0.5))

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[allong]

Thanks but I am not dealing with dates I am dealing with time. I tried your formula. It gave me a decimal, using my time fields, but I need to convert that decimal into hours. Any suggestion?

 

[scriverb]

I understand that you are working with a Time value but all time and date values are stored the same in ACCESS. The Date part of a date/time field is stored as the whole number to the left of the decimal. The time in a date/time field is stored to the right of the decimal. The time decimal value is the amount of time that since midnight that has passed within the 24 hour timespan. So, if it is 8:32:02 PM then the decimal portion of the date/time field is 0.855578703703704. If you multiply that times 24 hours you will get the numer of hours and minutes since midnight.(20.5338888888889 hours)

Is this what you are trying to do with your Time conversion. If not please post back with more explaination.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[allong]

Not exactly. I am working on a time sheet. StartTime is 8:00 PM - EndTime is 2:00 AM this is a total of 6 hours. I would like to see my total hours then sum them up for each person. The code I am using is =format([starttime]-1 -[endtime], &quot;Short Time&quot it gives me total hours but I get them in Short time form 6:00. I tried to convert the time using =TimeToDec([MyTime]) and I get #error. It just will not convert the short time to a decimal. I hope it gives you some idea what I a trying to do. Thanks again

 

[scriverb]

Well when converting elapsed time to a decimal so that they can be summed requires a little manipulation. I have indicated a few threads that have already addressed this process in a number of ways. I am also displaying a little bit of VBA code that I wrote to perform this function. It includes the date process but if there is only time involved in the field it has no consequence. Just use what you need to meet your needs. The code could be put in a Function and called either in a control or a query.

Dim vDays As Integer
Dim vHours As Integer
Dim vMinutes As Integer
Dim vTime As Double
Dim vTotalMinutes As Long
vTotalMinutes = DateDiff(&quot;n&quot;, CheckIN, CheckOut)
vDays = CInt(DateDiff(&quot;n&quot;, CheckIN, CheckOut) / 1440)
vHours = (CInt(DateDiff(&quot;n&quot;, CheckIN, CheckOut)) - (vDays * 1440)) / 60
vMinutes = (CInt(DateDiff(&quot;n&quot;, CheckIN, CheckOut)) - (vDays * 1440)) - (vHours * 60)
vTime = vHours + (vMinutes / 60)
MsgBox &quot;Total Minutes: &quot; & vTotalMinutes & vbCrLf & &quot;Days: &quot; & vDays & &quot; Hours: &quot; & vHours & &quot; Minutes: &quot; & vMinutes & vbCrLf & vbCrLf & &quot;Elapsed time: &quot; & vTime

Threads:
thread705-542015
thread181-532313
thread181-529272

Let me know if this works for you.


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[GingerR]

did you first define your TimeToDec function? it is not a standard Access thing, it's something you have to define.

see this:

http://support.microsoft.com/default.aspx?kbid=210185

instead, you can do this:

cdbl(MyTime)*24

which is what that function does anyhow.

 

[CosmoKramer]

In a new module copy and paste this function:

Public Function TimeElapsed(dIn As Variant, dOut As Variant) As Double
    If dOut - dIn > 0 Then
        TimeElapsed = ((CInt(Int(Format(dOut - dIn, "General Number"))) * 24) + DatePart("h", dOut - dIn)) + (DatePart("n", dOut - dIn) / 60)
    Else
        TimeElapsed = "0"
    End If
End Function

Add a field to your query to hold the calculated value. Something like:

TotalHours: TimeElapsed([timein],[timeout])

So, 05/07/03 18:30 to 05/08/03 10:00
would return: 15.5

 

[scriverb]

CosmoKramer: I covered your code with the first recommended thread that I posted. I wouldn't leave you out of this one as yours is a really excellent Function.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[allong]

CosmosKramer: Using your function code is simple enough, but I get a &quot;0&quot; in some totals. If I use starttime-endtime I get &quot;0&quot; in total where the hours passed midnight (8:00 PM to 1:00 AM). If i reverse the code (endtime-starttime)I get &quot;0&quot; in the other totals. I'm on the right track but there is a piece missing.

 

[scriverb]

If you change your assignment of Time() to the CheckIn and CheckOut to Now() you also pickup the date value. Your Format can control what is seen in the control and on the reports but then the code I provided for you will always work even over the midnight situation. You are first of all calculating the number of minutes between two full date and time values. Then converting those values to hours and minutes and ultimately to a decimal value that reflects the number of hours.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Spyder757]

A far less complex, but just as effetive solution is to just make a table with 2 fields.

One has the short time
The other has the decimal equivelent.

Spyder757

 

[Spyder757]

Anytime you need to convert back and forth just do a join between the fields.

Spyder757

 

[CosmoKramer]

Thanks Bob....As you mentioned, the function I posted was designed to utilize General date fields to be able to handle time ranges as the example posted above (8:00 PM to 1:00 AM)......