How to calculate total time duration

[mifee]

hi..

i have tbl workinghours
inside the tbl there are

-staff id
-date
- time in - data/time format : shorttime
- time out - data/time format : shorttime

in query..i already can count the duration..

eg.

time in 8.30 time out 11.30
duration = 03.:00

now.. i want to calculated total duration
and then the total duration i want to convert to day
the formula = totalduration/8
answer = in day

need help for that..

thanks

 

[recyan]

Till someone comes along, just check if below gives some guidelines :

SELECT 
	myTable.staffid, 
	myTable.TheDate, 
	Sum(DateDiff("h",[timeIn],[timeOut])/8) AS TheTotalDurationDividedBy8
FROM 
	myTable
GROUP BY 
	myTable.staffid, 
	myTable.TheDate;

Thanks

 

[John Big Booty]

Have a look at the DateDiff() function.

 

[mifee]

here the pic what i want to do..

 

[John Big Booty]

You should have no trouble achieving that with the DateDiff() function. This article gives one method to achieve this.

 

[mifee]

i have -ve value...(refer attachmenet)

and my query..

Duration: Format([ReplacementLeave]![time in]-1-[ReplacementLeave]![time out],"Short Time")

Minutes: DateDiff("n",[time in],[time out])


how to count total hours..:banghead::banghead:

 

[John Big Booty]

Perhaps this will help where times go across midnight.

 

[gemma-the-husky]

you can just add times together.

dim totaltime as date
totaltime = dsum(times)

- now the problem is that if you try to view the total time as a time, it will wrap if greater than 24hours, so a totaltime of 26 hours will just show as 2hrs. a total time of 52 hours, will show as 4hours (ie 4 hrs in excess of 48 hours)

- however the REAL total stored in access will be 1 day, 2 hrs, or 2 days, 4hrs in the examples.

- so we can change the time to a real number of hours or minutes, quite easily

elapsedrealhours = totaltime * 24
elapsedrealminutes = totaltime * 24 * 60

hope this idea helps