1. Registered User
Join Date
Sep 2009
Posts
6

Hi All,

I have created a simple access package to monitor producivity within my team, i have a table with a couple of columns, (Name,Task,Started,Finish,Time Taken)

started and finish are both formatted to take short time, i need time taken to display the amount of hours and minutes betweetn start and finish?

how can i do this?

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
ok so first off you have redundant data there, you have the start time, the end time so you don't need the timetaken (or you could have start time and time taken and derive time finished)

just format the timetaken with an appropriate mask assuming the time take is a datetime column
if not
=format(finish-start,"HH:MMS")
'you may need to tweak the format mask, I get confused between the various placeholders for minutes/months and so on

or take advantage of a quirk of the way Access stores time, which is a decimal number. the integer protion is the number of days since (IIRC 01/01/1900) and the decmal part is the proportion of days

so .5 = 12 hours
.75 is 18 hours and so on

so to find the number og hours

NoHours=timetaken/(1/24)

3. Registered User
Join Date
Sep 2009
Posts
6
hi i get what your saying but, i am inputting data from a sheet that shows me the time a task was started and a time that it was finished, i then need to know how long was spent on it

4. Moderator
Join Date
Jun 2005
Location
Richmond, Virginia USA
Posts
2,764
1. Do your Started and Finished fields include the dates as well as times?
2. Do the start/finish times ever cross midnight?

If you're entering Date and Time in Started and Finished this placed in the Control Source for TimeTaken will do the trick. And as healdem has suggested, this should be a calculated, unbound textbox. Any time you need the TimeTaken you would simply re-calculate it, using the same expression.
Code:
=Format(DateDiff("n",[Started],[Finished])\60 & ":" & DateDiff("n",[Started],[Finished]) Mod 60,"Short Time")
If you're only entering times question # 2 above becomes particularly important.

5. Registered User
Join Date
Sep 2009
Posts
6
hi,

start and finish are both formatted as just short time, and no they dont cross midnight as is a primarily 9-5 sort of thing

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
start and finish may be displayed as short time, but they are datetime datatypes. that measn you can do date arithmetic on the columns.

so you have a choice
use the datediff function
use an appropriate format on the timetaken column
do some simple ,maths on the column

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•