Percentages based on Short Time fields 1

[ajaeger]

I have a table (tblTime) that tracks the amount of time someone spends working in a certain area. The field (tblTime.TimeEntered) is defined as a Short Time field. Data entered might be:

DateEntered TimeEntered Area
7/1/11 1:10 Marketing
7/1/11 2:00 Administration
7/1/11 1:30 Management
7/1/11 1:10 Logistics
7/1/11 1:10 Personal
7/1/11 1:00 Vacation

In this example, the daily total would be 8:00. I have this working as =Sum([TimeEntered])

Next, I need to calculate the percentage of an 8 hour day that this total is. In this case, I need to see that 8:00/8 hours per day is 100%. In the text box, I have =(Sum([TimeEntered])/8). This returns 1:00. Almost close enough, but I'd like to get this to display 100% instead of 1:00. Changing the format of the text box doesn't work. Is there a way to do this?

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[dhookom]

IMO, you should be storing durations of time in a numeric field as the number of minutes or hours. Date/time field should be used for storing a point in time. You don't work 2 O'Clock, you work 2 hours or 120 minutes.

If you choose to leave your duration stored as a date/time, you will need to understand that 1 hour is 1/24 of a day and 8 hours is 1/3 of a day. Try multiplying the sum of your durations * 3.

#8:00#*3 = 1 = 100%
#4:00#*3 = 0.5 = 50%
#6:00#*3 = 0.75 = 75%


Duane
Hook'D on Access
MS Access MVP

 

[ajaeger]

you should be storing durations of time in a numeric field as the number of minutes or hours"

That was my thought too, but I just couldn't get it to work. They initially said 1/4 hour increments were OK, so I had that working an assumed all staff could figure out how many hours/minutes 1.75 was. But then they decided that they had to track down to 1 minute intervals (!) So easy decimals are out of the question. I had also tried spliting it into 2 fields - one for hours and the other for minutes. All of the summing was proving to be a pain in the butt since I had to calculate total minutes, figure out if it was more than 1 hour and if so, pull the hours out and add to the hour total and return just the number of minutes under 60.

The only thing I could figure out was the Short Time or go back to the 2 field Hour and Minute option and just deal with tons of calculations.

Any thoughts. Are there negative implications to using the Short Time in this manner?

Thanks for the tips to multiply by 3. That gives me what I need.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[dhookom]

An issue with summing the durations is the confusion when the amount is greater than 24 hours. 26 hours and 30 minutes might appear in a form or report as 2:30. You would have to work around this.

I would store the time duration as minutes only. If users can't figure out the math, you can provide some interface such as a combo box or unbound text boxes with some code in the after update.

Duane
Hook'D on Access
MS Access MVP