Create a running sum for time spent on a job

  • Thread starter Thread starter Darrell
  • Start date Start date
D

Darrell

I am trying to create a running sum field for a time
keeping database. I have two tables (clock in and clock
out) with date and time fields so when a person selects
the appropriate "button" fro the start screen, the fields
(clock in/out date and clock in/out time) are
automatically populated. I am using a medium time format
and the value upon button "click" is "Time()" . I then
created a query where I could calculate total time by
using "[CLOCKOUT Time] - [CLOCKIN TIME]" resulting in
something like 7:35. The problem is trying to create a
running sum to create a weekly or bi weekly total. Every
time the total reaches 24:00 it resets to 00:00 and starts
summing all over again.

Any help out there?
 
You are treating the difference and total as times, they are not. A time is a certain
point in the day. The difference (time worked that day) and total (time worked for the
week) are just numbers, not times.

Since minutes is the smallest unit that you want, start by finding the difference in
minutes (we'll compute back to hours and minutes later).

intMinutesWorked = DateDiff("n", StartTime, EndTime)

Do this for each day. You can now take the minutes worked each day to find the total
minutes worked for the week without the problem as you pass 24 hours.

To get hours and minutes from minutes:

intHours = Int(intTotalMinutes /60)
intMinutes = intTotalMinutes Mod 60
 
Thank you so much for the info...i have managed to get
total minutes for each day which I use a running sum to
calculate total minutes. From that field I calculate
total hours using intHOurs=Int(intTotalMinutes/60),
however, when I use the

intMinutes=intTotalMinutesMod60

i get total minutes that exceed an hour. eg. 138 minutes
which should be 2 hrs and 18 minutes which needs to be
added into the final hours total. The resulting
information i want to be displayed is:

TOTAL HOURS AND MINUTES FOR WEEK: 42hrs 48mins


what am i doing incorrectly?

thanks again

-----Original Message-----
You are treating the difference and total as times, they are not. A time is a certain
point in the day. The difference (time worked that day) and total (time worked for the
week) are just numbers, not times.

Since minutes is the smallest unit that you want, start by finding the difference in
minutes (we'll compute back to hours and minutes later).

intMinutesWorked = DateDiff("n", StartTime, EndTime)

Do this for each day. You can now take the minutes worked each day to find the total
minutes worked for the week without the problem as you pass 24 hours.

To get hours and minutes from minutes:

intHours = Int(intTotalMinutes /60)
intMinutes = intTotalMinutes Mod 60

--
Wayne Morgan


Darrell said:
I am trying to create a running sum field for a time
keeping database. I have two tables (clock in and clock
out) with date and time fields so when a person selects
the appropriate "button" fro the start screen, the fields
(clock in/out date and clock in/out time) are
automatically populated. I am using a medium time format
and the value upon button "click" is "Time()" . I then
created a query where I could calculate total time by
using "[CLOCKOUT Time] - [CLOCKIN TIME]" resulting in
something like 7:35. The problem is trying to create a
running sum to create a weekly or bi weekly total. Every
time the total reaches 24:00 it resets to 00:00 and starts
summing all over again.

Any help out there?
Click to expand...


.
Click to expand...
 
If what you have printed here is accurate, you need a space on each side of the word Mod

intMinutes = intTotalMinutes Mod 60

Mod is an operator, it returns the remainder of intTotalMinutes/60. If you can't get Mod
to work for you, you can also use

intMinutes = intTotalMinutes - (intHours * 60)

--
Wayne Morgan


Darrell said:
Thank you so much for the info...i have managed to get
total minutes for each day which I use a running sum to
calculate total minutes. From that field I calculate
total hours using intHOurs=Int(intTotalMinutes/60),
however, when I use the

intMinutes=intTotalMinutesMod60

i get total minutes that exceed an hour. eg. 138 minutes
which should be 2 hrs and 18 minutes which needs to be
added into the final hours total. The resulting
information i want to be displayed is:

TOTAL HOURS AND MINUTES FOR WEEK: 42hrs 48mins


what am i doing incorrectly?

thanks again

-----Original Message-----
You are treating the difference and total as times, they are not. A time is a certain
point in the day. The difference (time worked that day) and total (time worked for the
week) are just numbers, not times.

Since minutes is the smallest unit that you want, start by finding the difference in
minutes (we'll compute back to hours and minutes later).

intMinutesWorked = DateDiff("n", StartTime, EndTime)

Do this for each day. You can now take the minutes worked each day to find the total
minutes worked for the week without the problem as you pass 24 hours.

To get hours and minutes from minutes:

intHours = Int(intTotalMinutes /60)
intMinutes = intTotalMinutes Mod 60

--
Wayne Morgan


Darrell said:
I am trying to create a running sum field for a time
keeping database. I have two tables (clock in and clock
out) with date and time fields so when a person selects
the appropriate "button" fro the start screen, the fields
(clock in/out date and clock in/out time) are
automatically populated. I am using a medium time format
and the value upon button "click" is "Time()" . I then
created a query where I could calculate total time by
using "[CLOCKOUT Time] - [CLOCKIN TIME]" resulting in
something like 7:35. The problem is trying to create a
running sum to create a weekly or bi weekly total. Every
time the total reaches 24:00 it resets to 00:00 and starts
summing all over again.

Any help out there?
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top