Convert Time field to Numeric

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I have a calculated field on a report that shows the
amount of time elapsed in Short Time format. I need to
convert this to a numeric field, if possible a field that
rounds to the nearest quarter hour. For example, the
calculation shows 1:20, I'd like to see 1.25.
Any suggestions?
Thanks,
Mary
 
Hi.
I think this formula in a query would do what you want:
TotFractTime: Val(Hour([ElapseTime]))+(Int((Minute
([ElapseTime])+14)/15)/4)
All if the formula on one line.
ElapseTime is the calculated time you have in your query.
Hope this helps
Fons
 
Hi. Sorry I did not identify myself on the original reply.
I think this formula in a query would do what you want:
TotFractTime: Val(Hour([ElapseTime]))+(Int((Minute
([ElapseTime])+14)/15)/4)
All if the formula on one line.
ElapseTime is the calculated time you have in your query.
Hope this helps
Fons
 
Hello. I tried this formula and get an error:
Extra ) in query expression '[TotFractTime: Val(Hour
([ActualHours]))+Int((Minute([ActualHours])+14)/15)/4)]'.

I tried removing the ) from different areas but still get
the error.

Thanks,
Mary
-----Original Message-----
Hi. Sorry I did not identify myself on the original reply.
I think this formula in a query would do what you want:
TotFractTime: Val(Hour([ElapseTime]))+(Int((Minute
([ElapseTime])+14)/15)/4)
All if the formula on one line.
ElapseTime is the calculated time you have in your query.
Hope this helps
Fons
-----Original Message-----
Hello,
I have a calculated field on a report that shows the
amount of time elapsed in Short Time format. I need to
convert this to a numeric field, if possible a field that
rounds to the nearest quarter hour. For example, the
calculation shows 1:20, I'd like to see 1.25.
Any suggestions?
Thanks,
Mary
.
Click to expand...
.
Click to expand...
 
I am not sure where you are putting this formula:
I just typed the formula in the field block in the query
TotFractTime: Val(Hour([ElapseTime]))+(Int((Minute
([ElapseTime])+14)/15)/4)
Yours should read:
TotFractTime: Val(Hour([ActualHours]))+(Int((Minute
([ActualHours])+14)/15)/4)
Note the "(" between the "+" and the "Int", All on one
line.
Hope this straightens it out.
FOns

-----Original Message-----
Hello. I tried this formula and get an error:
Extra ) in query expression '[TotFractTime: Val(Hour
([ActualHours]))+Int((Minute([ActualHours])+14)/15)/4)]'.

I tried removing the ) from different areas but still get
the error.

Thanks,
Mary
-----Original Message-----
Hi. Sorry I did not identify myself on the original reply.
I think this formula in a query would do what you want:
TotFractTime: Val(Hour([ElapseTime]))+(Int((Minute
([ElapseTime])+14)/15)/4)
All if the formula on one line.
ElapseTime is the calculated time you have in your query.
Hope this helps
Fons
-----Original Message-----
Hello,
I have a calculated field on a report that shows the
amount of time elapsed in Short Time format. I need to
convert this to a numeric field, if possible a field that
rounds to the nearest quarter hour. For example, the
calculation shows 1:20, I'd like to see 1.25.
Any suggestions?
Thanks,
Mary
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Thank you, it's working now. I had to remove the +14, the
numbers are matching up without it.
Thanks for your help!
Mary
-----Original Message-----
I am not sure where you are putting this formula:
I just typed the formula in the field block in the query
TotFractTime: Val(Hour([ElapseTime]))+(Int((Minute
([ElapseTime])+14)/15)/4)
Yours should read:
TotFractTime: Val(Hour([ActualHours]))+(Int((Minute
([ActualHours])+14)/15)/4)
Note the "(" between the "+" and the "Int", All on one
line.
Hope this straightens it out.
FOns

-----Original Message-----
Hello. I tried this formula and get an error:
Extra ) in query expression '[TotFractTime: Val(Hour
([ActualHours]))+Int((Minute([ActualHours])+14)/15)/4)]'.

I tried removing the ) from different areas but still get
the error.

Thanks,
Mary
-----Original Message-----
Hi. Sorry I did not identify myself on the original reply.
I think this formula in a query would do what you want:
TotFractTime: Val(Hour([ElapseTime]))+(Int((Minute
([ElapseTime])+14)/15)/4)
All if the formula on one line.
ElapseTime is the calculated time you have in your query.
Hope this helps
Fons
-----Original Message-----
Hello,
I have a calculated field on a report that shows the
amount of time elapsed in Short Time format. I need to
convert this to a numeric field, if possible a field that
rounds to the nearest quarter hour. For example, the
calculation shows 1:20, I'd like to see 1.25.
Any suggestions?
Thanks,
Mary
.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top