HOUR MINUTE FORMAT AND CALCULATION

[ybarot]

Hello everyone,
I am new to this forum, this is my first time. I hope someone can help me with my problem. I am creating a small database using Access at my work place. I came forward with 2 problems.
1. I need to create a field (Down Time) in a form that can store hours and minutes like 40:30. If I format the field as Short Date it won't allow me value higher than 23:59. Also, let you know that I am using the field (Down Time) in a calculation.i.e. Run Time - Down Time = Actual Time.

2.I also need a help on for the following calculation.
Unit per Minute =Quantity(number field)/Actual Time.
Your help is greatly appriciated.

 

[Ed2020]

A date/time field is designed to store a specific date and time of day. Short Date format is allowing you to enter a moximum value of 23:59 (one minute to midnight) as this is the latest time this format will allow.

You could use a numeric field of type integer and store the down time in minutes only (so two hours 30 minutes would be stored as 150 minutes), or you could use a numeric field that allows decimal places however this will mean converting minutes into fractions of an hour (for example 4 hours 45 mins would need to be entered as 4.75).

Alternatively you could have two fields - one for hours, one for minutes.

There are lots of ways of implementing this with varying degrees of complexity...

Ed Metcalfe.

Please do not feed the trolls.....

 

[dhookom]

You should not be storing durations of time in a date/time field. Decide on a unit of measure such as minutes or seconds and store values in a numeric field. If your smallest segment of time is minutes, then create a field of [DownTimeMinutes] with a data type of Long or Double. Store the number of minutes.

This will also help you resolve your second question.

Date/Time fields are for storing a point in time.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dhookom]

Sorry. I should have refreshed my screen prior to submitting to see Ed's response which is basically the same as mine other than his is more complete ;-)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ybarot]

Thanks Ed, Thanks dhookom. I will work on your suggestions and post again if I need anymore help. Thanks again.

 

[ybarot]

Hello, I am back again. The suggestion you made will work fine, but there is another problem which I should have mentioned before. My form fields are like this.

Start Time (Date /Time field): 10/30/07 9:00:00 AM
End Time (Date/Time field): 11/1/07 10:30:00 AM
Down Time (Field Type?): 38:30 (hrs/mins)
Quantity (Number):10000
Auto calculation field:
Actual Time(field Type?)=(End Time-Start Time)-(Down Time)
U per Minutes = Quantity/Actual Time
I am able to achieve all that only problem is it won't take more than 23:59.
Hope you can help me, I am trying to work this for days. Thanks.

 

[SkipVought]

Date/Time units are DAYS. If your DownTime is stored as Hours, you must convert to days.

Actual Time(field Type?)=(End Time-Start Time)-(Down Time)/24

If your DownTime is stored as Hours & Minutes, you must convert each to days.

Actual Time(field Type?)=(End Time-Start Time)-((Down Hours)/24 + (Down Minutes)/24/60)

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]