Converting HH:MM to decimal

  • Thread starter Thread starter Job
  • Start date Start date
J

Job

I have a field "Accesstime" which is in hh:mm format. I'm summing the times
to come up with say 11:21. I would like this to be the decimal equivalent.

Any help is appreciated.
 
Summing times?

What is 3 o'clock plus 4 o'clock?

You can't sum times.

You can sum DURATIONS. You can't store a duration as a time though.

What is your actual field? Is it a date/time field? Is it a number field?
Is it a text field?

Durations should be stored as numbers. 11.35 is the same thing as eleven
hours and 21 minutes.
 
Yes, sorry. I have durations of time...6:30,1:20. These are in date/time
format and when I sum I get 7:50. But what I really want is the decimal
equivalent of total duration.
 
Job said:
Yes, sorry. I have durations of time...6:30,1:20. These are in
date/time format and when I sum I get 7:50. But what I really want
is the decimal equivalent of total duration.
Click to expand...

The point Rick B was trying to make is that while humans express the "Time"
6:30 and the "Duration" six hours and thirty minutes both as "6:30". To
Access this value in a DateTime field ALWAYS means the time 6:30.

While you can do time math on DateTime fields to a limited degree because
the values are actually stored as numbers under the covers you will
encounter problems when you cross midnight or if sums exceed 24 hours.

So what you should be doing is storing your durations as numbers in the
first place rather than using a DateTime field.
 
You haven't suggested the time increment of the duration. If you want to
display in hours, multiply your Sum([timefield]) by 24.

6:30 is stored as 0.2708 and 1:20 is 0.0556
 
I incorrectly posted my first question. The duration is actually in Minutes
and Seconds. So 6:30 is six minutes and thirty seconds. This causes
problems when trying to convert.

Thanks

Duane Hookom said:
You haven't suggested the time increment of the duration. If you want to
display in hours, multiply your Sum([timefield]) by 24.

6:30 is stored as 0.2708 and 1:20 is 0.0556

--
Duane Hookom
MS Access MVP
--

Rick Brandt said:
The point Rick B was trying to make is that while humans express the
"Time"
6:30 and the "Duration" six hours and thirty minutes both as "6:30". To
Access this value in a DateTime field ALWAYS means the time 6:30.

While you can do time math on DateTime fields to a limited degree because
the values are actually stored as numbers under the covers you will
encounter problems when you cross midnight or if sums exceed 24 hours.

So what you should be doing is storing your durations as numbers in the
first place rather than using a DateTime field.
Click to expand...
Click to expand...
 
I incorrectly posted my first question. The duration is actually in Minutes
and Seconds. So 6:30 is six minutes and thirty seconds. This causes
problems when trying to convert.
Click to expand...

What Rick was saying still stands:

Access Date/Time field types are not really suitable for storing
durations - period. They're good for storing precise moments of date
and time, and that's about it.

I'd suggest that you use a Long Integer field to store total seconds
(390 in this example). You can display it as 6:30 with an expression

[Duration] \ 60 & Format([Duration] MOD 60, ":00")

and you can do data entry using a Form with two unbound textboxes,
minutes and seconds, with AfterUpdate code to calculate total seconds
and update the fields.

John W. Vinson[MVP]
 
What are the problems? We are having trouble seeing your SQL, data records,
error messages,...

--
Duane Hookom
MS Access MVP


John Vinson said:
I incorrectly posted my first question. The duration is actually in
Minutes
and Seconds. So 6:30 is six minutes and thirty seconds. This causes
problems when trying to convert.
Click to expand...

What Rick was saying still stands:

Access Date/Time field types are not really suitable for storing
durations - period. They're good for storing precise moments of date
and time, and that's about it.

I'd suggest that you use a Long Integer field to store total seconds
(390 in this example). You can display it as 6:30 with an expression

[Duration] \ 60 & Format([Duration] MOD 60, ":00")

and you can do data entry using a Form with two unbound textboxes,
minutes and seconds, with AfterUpdate code to calculate total seconds
and update the fields.

John W. Vinson[MVP]
Click to expand...
 
Actually I fixed the first problem. The data I was recieving was being
'converted' by Excel and the 6:30 was being interpreted as 6:30 AM. Now I'm
getting the raw text file. The next step I would think would be either to
create a field that converts the data by parsing the data into two segments
and then setting the formats for each of the fields for MM and SS or
something like that. What do you think?


Duane Hookom said:
What are the problems? We are having trouble seeing your SQL, data
records, error messages,...

--
Duane Hookom
MS Access MVP


John Vinson said:
I incorrectly posted my first question. The duration is actually in
Minutes
and Seconds. So 6:30 is six minutes and thirty seconds. This causes
problems when trying to convert.
Click to expand...

What Rick was saying still stands:

Access Date/Time field types are not really suitable for storing
durations - period. They're good for storing precise moments of date
and time, and that's about it.

I'd suggest that you use a Long Integer field to store total seconds
(390 in this example). You can display it as 6:30 with an expression

[Duration] \ 60 & Format([Duration] MOD 60, ":00")

and you can do data entry using a Form with two unbound textboxes,
minutes and seconds, with AfterUpdate code to calculate total seconds
and update the fields.

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
To convert a text value like "6:30" to a number, consider this expression:
Val(Left("6:30",Instr("6:30",":")-1)) * 60 +
Val(Mid("6:30",Instr("6:30",":")+1))

--
Duane Hookom
MS Access MVP


Job said:
Actually I fixed the first problem. The data I was recieving was being
'converted' by Excel and the 6:30 was being interpreted as 6:30 AM. Now
I'm getting the raw text file. The next step I would think would be
either to create a field that converts the data by parsing the data into
two segments and then setting the formats for each of the fields for MM
and SS or something like that. What do you think?


Duane Hookom said:
What are the problems? We are having trouble seeing your SQL, data
records, error messages,...

--
Duane Hookom
MS Access MVP


John Vinson said:
I incorrectly posted my first question. The duration is actually in
Minutes
and Seconds. So 6:30 is six minutes and thirty seconds. This causes
problems when trying to convert.

What Rick was saying still stands:

Access Date/Time field types are not really suitable for storing
durations - period. They're good for storing precise moments of date
and time, and that's about it.

I'd suggest that you use a Long Integer field to store total seconds
(390 in this example). You can display it as 6:30 with an expression

[Duration] \ 60 & Format([Duration] MOD 60, ":00")

and you can do data entry using a Form with two unbound textboxes,
minutes and seconds, with AfterUpdate code to calculate total seconds
and update the fields.

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top