Convert number to Hours and Minutes

C

Cadburys

Hi All

Please could someone tell me if this is possible. I have 3 number fields -
TypeTime, ResearchTime and ProofTime. Users will enter in minutes the time
it has taken to complete a job eg. 70 , 30 and 35.

I have a query that then adds up these 3 fields as follows
TimeSpent=[TypeTime]+[ResearchTime]+[ProofTime] my answer is obviously 135.

I would then like to have another calculation to convert 135 to 2 hours 15
min. Dividing by 60 does not work as the value returned is 2.25.

Any help would be appreciated - even if to tell me that it is not possible.
 
P

peregenem

Cadburys said:
Please could someone tell me if this is possible. I have 3 number fields -
TypeTime, ResearchTime and ProofTime. Users will enter in minutes the time
it has taken to complete a job eg. 70 , 30 and 35.

I have a query that then adds up these 3 fields as follows
TimeSpent=[TypeTime]+[ResearchTime]+[ProofTime] my answer is obviously 135.

I would then like to have another calculation to convert 135 to 2 hours 15
min. Dividing by 60 does not work as the value returned is 2.25.
Click to expand...

Instead of storing the duration in one column, use two columns
(start_date and end_date) to capture an interval them you don't have to
reinvent all the temporal functions which Jet and Access supplies and
you get better audits!
 
J

John Spencer (MVP)

Actually, if you are storing durations of time versus points in time, it is
usually better to use a number field and store the number of units of time
(hours, minutes, seconds) that you are interested in.

On the other hand, if you need to know the start and end time (points in time)
then I completely agree with you.

If the only thing you need is duration, then store that. Storing two datetime
fields and then manipulating them to get a duration for each pair and then
adding them and then forcing that sum of time durations back into a classic hour
and minute time frame is much more complex.

Please could someone tell me if this is possible. I have 3 number fields -
TypeTime, ResearchTime and ProofTime. Users will enter in minutes the time
it has taken to complete a job eg. 70 , 30 and 35.

I have a query that then adds up these 3 fields as follows
TimeSpent=[TypeTime]+[ResearchTime]+[ProofTime] my answer is obviously 135.

I would then like to have another calculation to convert 135 to 2 hours 15
min. Dividing by 60 does not work as the value returned is 2.25.
Click to expand...

Instead of storing the duration in one column, use two columns
(start_date and end_date) to capture an interval them you don't have to
reinvent all the temporal functions which Jet and Access supplies and
you get better audits!
Click to expand...
 
V

Van T. Dinh

Try:

Format$([TimeSpent] \ 60, "0") & " hour(s) " &
Format$([TimeSpent] MOD 60, "00") & " minute(s)"

(type as 1 line)

Note the backlash \ for integer division which is different from the normal
division /.
 
P

peregenem

John said:
Actually, if you are storing durations of time versus points in time, it is
usually better to use a number field and store the number of units of time
(hours, minutes, seconds) that you are interested in.
Click to expand...

I have my "management" head on. I'd rather my people tell me when they
start and finish a job (and break for lunch etc). It avoids those 25
hour days :)
 
C

Cadburys

Hi All

Dennis - thanks for the help - it worked!! I had thought about having start
and end times and calculating but I would end up with too many fields.

Anyway thanks for all the input!
--
Cheers


Van T. Dinh said:
Try:

Format$([TimeSpent] \ 60, "0") & " hour(s) " &
Format$([TimeSpent] MOD 60, "00") & " minute(s)"

(type as 1 line)

Note the backlash \ for integer division which is different from the normal
division /.

--
HTH
Van T. Dinh
MVP (Access)




Cadburys said:
Hi All

Please could someone tell me if this is possible. I have 3 number fields -
TypeTime, ResearchTime and ProofTime. Users will enter in minutes the
time
it has taken to complete a job eg. 70 , 30 and 35.

I have a query that then adds up these 3 fields as follows
TimeSpent=[TypeTime]+[ResearchTime]+[ProofTime] my answer is obviously
135.

I would then like to have another calculation to convert 135 to 2 hours 15
min. Dividing by 60 does not work as the value returned is 2.25.

Any help would be appreciated - even if to tell me that it is not
possible.
Click to expand...
Click to expand...
 
Top