Time calculation/coversion

Markvand

Registered User.
Local time
Today, 15:38
Joined
Jul 13, 2004
Messages
88
Time calculation/conversion problem

Hi all,

I have a problem with time conversion, I'm using this function, it works fine, the only problem is, I'm getting a result as a string (that was the only way I could write this - limited skills :rolleyes: ). What I want to do is to have a result as integer, single, etc. as long as I could use this value for further calculations.

Public Function ElapsedHrs(DS As Date, TS As String, DE As Date, TE As String) As String
'Returns elapsed hrs with minutes
'DS = Start Date
'TS = Start Time in 24 hr (military) format hh:mm
'DE = End Date
'TE = End Time in 24 hr (military) format hh:mm

Dim dhrs As Single, ehrs As Single, tdays As Integer
'Calculate # of days
tdays = DateDiff("d", DS, DE) * 1440
'Calculate # of minutes
ehrs = DateDiff("n", TS, TE)
'Format # of days in minutes
dhrs = tdays / 60
'Calculate elapsed hours
ElapsedHrs = Int((ehrs + tdays) / 60) & " hr " & (ehrs + tdays) Mod 60 & " min"
End Function


Any help would be much appreciated.

Cheers
Mark
 
Last edited:
Please help I really need one here!

Thanx in advance
 
Could you dim ElapsedHrs() As Variant

???
kh
 
Markvand, you can not change the type on this. It will remain a string, even if you dim as variant. As long as you have, string characters in it, "min" & "hrs"
etc..., it will result as a string.
I would Dim as a Single type & format Accordingly.

ElapsedHrs = (Int((ehrs + tdays) / 60) & "." & (ehrs + tdays) Mod 60

..So, for example ElapsedHrs = 54.32

of course, their respective values are unclear, but, I believe your options, are only along these lines.

Possibly use 2 functions, one for hrs, the other for minutes?

ControlSource = ElapsedHrs & "." & ElapsedMins / 2 (not sure if syntax for calculation, is correct?)

ControlSource = Elapsedhrs / 2 & "." & Elapsedmns / 2

I like the Single Format better?

Hope this helps, good luck!
 
Hi Db7,

Thanks for you help, I'll play around with it and see how it goes, anyway appreciate yr help.

Regards

Mark

Any other ideas are welcomed ;)
 
Last edited:
You can return it as a double which would give you decimal hours

Public Function ElapsedHrs(DS As Date, TS As String, DE As Date, TE As String) As Double
'Returns elapsed hrs
'DS = Start Date
'TS = Start Time in 24 hr (military) format hh:mm
'DE = End Date
'TE = End Time in 24 hr (military) format hh:mm
DS = DS + CDate(TS)
DE = DE + CDate(TE)
ElapsedHrs = (DE - DS) * 24 ' turn days into hours
End Function

Better though is to retun it as date/time and use formating to display the results in hours min.

Public Function ElapsedHrs(DS As Date, TS As String, DE As Date, TE As String) As Date
'Returns elapsed hrs
'DS = Start Date
'TS = Start Time in 24 hr (military) format hh:mm
'DE = End Date
'TE = End Time in 24 hr (military) format hh:mm
DS = DS + CDate(TS)
DE = DE + CDate(TE)
ElapsedHrs = (DE - DS)
End Function


HTH

Peter
 
Markvand said:
Public Function ElapsedHrs(DS As Date, TS As String, DE As Date, TE As String) As Date
dhrs = tdays / 60
'Calculate elapsed hours
ElapsedHrs = cdate(Int((ehrs + tdays) / 60) & ":" & (ehrs + tdays) Mod 60 & ":00"
End Function
Your output is in a string.. just enclose that with the function cdate() which converts string to datevalue....??

Just a thought.


Vince
 
Thanks you guys for all yr replies,

I've got it, the solution was easier than I thought it would be,

Public Function ElapsedHrs(DS As Date, TS As String, DE As Date, TE As String) As Single
'Returns elapsed hrs with minutes
'DS = Start Date
'TS = Start Time in 24 hr (military) format hh:mm
'DE = End Date
'TE = End Time in 24 hr (military) format hh:mm
Dim dhrs As Single, ehrs As Single, tdays As Integer
'Calculate # of days
tdays = DateDiff("d", DS, DE) * 1440
'Calculate # of minutes
ehrs = DateDiff("n", TS, TE)
'Format # of days in minutes
dhrs = tdays / 60
'Calculate elapsed hours
ElapsedHrs = (ehrs + tdays)
End Function

When I call the function in after click event I use the formated ElapsedHrs (int()/60 & ":" & () mod 60) but in the table I store the value like it is in the function (as Single) thus I can use it to further calculations.

Regards

Mark
 

Users who are viewing this thread

Back
Top Bottom