Field for Minutes and Seconds

[Guest]

On 1/2/2007 I was given a good suggestion to store time duration. I needed
to store in a numberic field as minutes and seconds then display it in any
format I want. The only problem is I'm having trouble taking this result and
storing it to a numeric field so I can do further calculations.

The formula used was =[SomeFieldName]\60 & Format([SomeFieldName] Mod 60,
"\:00) This worked fine. Now I need to store this unbound textbox
value in a field to gather data and do further calculations. Like averaging
3:30 and 5:30. These are 3minutes,30seconds duration and 5minutes,30seconds
duration. I tried a Macro using SetValue to send a single duration value
like 3:30 to a numeric field but this does not work. Any ideas?

 

[Franck]

im not sure of you problem but you dont show your minute in the good
format
3min 30 sec is : 00:03:30
5min 30 sec is : 00:05:30
or he take the first nubmer for hours if you dont specify 3 columns

 

[Ron2006]

im not sure of you problem but you dont show your minute in the good
format
3min 30 sec is : 00:03:30
5min 30 sec is : 00:05:30
or he take the first nubmer for hours if you dont specify 3 columns

The field where you are storing the information should simply be an
integer field. For instance the 3 min 30 second value should be 210
stored in an interger field.

Don't try to store the formated value, but the original value that you
formated for display purposes.

If you decide to try to use the 00:00:00 format I am not sure it will
make sense either because when your field ever has more than 60
minutes in it, it will confuse people since they will be expecting the
hours to be separated as hours (unless the mod 60 handles that, which
I do NOT know.)

Ron

 

[Guest]

Hi Ron,
Thanks for your input. Where can I read up on Mod 60 and how this works? I
think I've reached a solution. I've converted hours and minutes to total
minutes. Then I average all the total minutes to get a report with average
time.

Example:
3:31 this equals 211 minutes
4:44 this equals 284 minutes
5:25 this equals 325 minutes

Total minutes = 820 (over a period such as January records)
Divide by 3 = 273 Average Minutes

273 Avg Minutes = 4.55 hrs Avg (this is what I want to display on report).

 

[Guest]

Ron,

Sorry for confusion. I'm working with two separate tables. One records
Minutes and Seconds and the other records Hours and Minutes. I'm trying to
acheive the same. A summary report showing average times (duration times)
like a timer and not clock time. I orginally was storing records in a
time/date field which cannot be calculated. Big job to fix and go back to
update records.

 

[Ron2006]

Ron,

Sorry for confusion. I'm working with two separate tables. One records
Minutes and Seconds and the other records Hours and Minutes. I'm trying to
acheive the same. A summary report showing average times (duration times)
like a timer and not clock time. I orginally was storing records in a
time/date field which cannot be calculated. Big job to fix and go back to
update records.
--
FL









- Show quoted text -

FL,
I think you have arrived at the normally suggest solution. Basically,
it comes down to converting to a number that represents the lowest
unit that you want to monitor and then do all math on that number. For
the minutes/seconds you will probably want to store the difference as
total seconds and then do the math on that. Although a stored value of
minutes that included the decimal (ie. 4.33 minutes) would also
probably give you what you want also.

Ron

 

[Ron2006]

here is a site that explains the mod 60 reference

http://allenbrowne.com/casu-13.html

Ron