Time conversion

[rwalle]

HI::
I have an Access Database that automatically collects data from an industrial proccess, each minute I have a new record, then I can count the records and have the amount of time the proccess was on or off, dividing by 60 I can have the Hours; so far fine , now I'd like to convert the resulting digital time into time with format like hh:mm, any ideas how to do it ?

Tks in advance for your help

Raymundo Walle

[Delerna]

unless I am misreading your question, you already asked this here

[Delerna]

On second read I think I understand now.
Do you mean you have a decimal value representing a time

ie
5.75 minutes as a decimal value which is 5:45 minutes as a time value

That will be a mathematics exercise

[rwalle]

Time Conversion

yes Delerna::
I have the decimal value 5.5 and need to have 5:30 Hrs

R Walle

[TheSmileyCoder]

Public Function fncTimeFormat(db_input As Double) As String
    'Number of hours
    Dim intHours As Integer
    intHours = Fix(db_input)
    
    'Number of mins
    Dim intMin As Integer
    intMin = (db_input - intHours) * 60
    
    'Output
    fncTimeFormat = Format(intHours, "00") & ":" & Format(intMin, "00")
End Function

There may be other smarter ways of doing it. This is just my 5 cents.


The value can ofcourse be converted to time units by using:

cdate(fncTimeFormat(5.5))

which would give the result:
05:30:00

[NeoPa]

To convert decimal minutes to a time-format value simply divide by 24 * 60.

Date/Times are stored as whole days and fractions of them, thus 5.5 would represent 5 1/2 days. Divide that by 24 to get 5.5 hours, then again by 60 to get 5.5 minutes.

To prove the value, simply format your result to see what turns up.

dblValue = 5.5

dblValue = dblValue / (24 * 60)  'x / 1440

Debug.Print Format(dblValue, "HH:nn:ss")

[Delerna]

Interesting Neopa and TheSmileyOne
Heres the approach I normally take within a query

[code=sql]
cstr( cint(DecVal) ) & ':' & cstr( 60 * ( DecVal - cint(DecVal) ) )[/code]
which is similat to TSO's first example


I might need to change that....if I can remember yours ... next time :)

[NeoPa]

I can see where you're coming from, but you should remember the result is supposed to be in numerical format. Converting the type of data for display purposes should only ever be done at the last stage, when interfacing with the user (display on screen etc). Otherwise you are working with foreign principles which need to be converted (for you automatically in most cases but nevertheless extra work). Sometimes such conversions allow unexpected issues into your calculations.

I'm probably overstating the isuue here, but I'd strongly suggest to any of you guys who are serious about your design to understand and work with the different formats data is stored in and worked with.

[rwalle]

Thanks all for your reply, I took Neopa approach since looks simplest way to do it, also thanks for the info about how the Time/ date are handled by Access it helps understand date time calculations

Raymundo Walle

[NeoPa]

You're welcome Raymundo :)

Welcome to Bytes!