Convert Time to Number 1

[mbarkley9872]

I have alot of fields that capture time - flight hours, engine on-wing hours, time since overhaul, etc.

They are formatted like hh:mm but the hour part is often 3-5 digits. I.e, 23756:21

I need to use this data to perform many types of calculations - utilization rates, sums, etc.

The data is imported once monthly and appended to the appropriate table. I'm thinking I should go ahead and create extra fields and convert the times to numbers once during the append. Then I'll have the data readily formatted for the zillions of queries that will use these times. Is this a bad idea?

And, could someone help me with the expression to convert it to a number. I've been attempting to figure this out on my own, but I'm having trouble trimming off the ":". This...

FltHrs_D: Left(UTILDWN!FltHrs,InStr(UTILDWN!FltHrs,":"))

... gives me the hours but still has the ":"
113:27 ends up as 113:

Thanks for any help!

Mike

 

[PHV]

FltHrs_D: Left(UTILDWN!FltHrs,InStr(UTILDWN!FltHrs,":")[!]-1[/!])

A simpler way:
FltHrs_D: Val(UTILDWN!FltHrs)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AlexCuse]

If you just want the hours, try this:

FltHrs_D: Left(UTILDWN!FltHrs,InStr(UTILDWN!FltHrs,":")[b]-1[/b])

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[mbarkley9872]

Thanks to both of you but I still need to add the minutes. The Val() is only returning the Hours.

Mike

 

[PHV]

NumberOfMinutes: 60*Val(UTILDWN!FltHrs)+Right(UTILDWN!FltHrs,2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mbarkley9872]

PHV,

I'd like to get it as hours... For example 100:45 would be 100.75

I tried CDbl(Val(UTILDWN!FltHrs) but no luck.

Thanks for the help!

Mike

 

[PHV]

NumberOfHours: Val(UTILDWN!FltHrs)+(Right(UTILDWN!FltHrs,2)/60)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886