Calculating Time Sheet

I have a form that gives the total hours worked each day, I would like to add
all these up to give a weekly total hours it of course only displays up
24hrs, how do I display the total if it goes over 24 hours for the week

Calculate the sum of the field for hours worked.
Assuming your form is filtered to show just the days from the current week,
use something like =Sum([HrsWorked]) in an unbound textbox in the form's
header or footer.
Where HrsWorked is the name of the field for hours worked in the table or
query behind the form.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"Northern Dave" wrote in message
...
I have a form that gives the total hours worked each day, I would like to
add
all these up to give a weekly total hours it of course only displays up
24hrs, how do I display the total if it goes over 24 hours for the week

Hello Jeanette, I will have to kick myself as I was obviously looking too
deep into codes etc instead of seeing the simple view, Thankyou so much

"Jeanette Cunningham" wrote:

Calculate the sum of the field for hours worked.
Assuming your form is filtered to show just the days from the current week,
use something like =Sum([HrsWorked]) in an unbound textbox in the form's
header or footer.
Where HrsWorked is the name of the field for hours worked in the table or
query behind the form.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"Northern Dave" wrote in message
...
I have a form that gives the total hours worked each day, I would like to
add
all these up to give a weekly total hours it of course only displays up
24hrs, how do I display the total if it goes over 24 hours for the week

I might be reading too much into your post, but the fact that you raise the
question of it being unable to sum above 24 hours makes me wonder whether you
are using a date/time data type as a basis for the HoursWorked? The
date/time data type stores values representing a point in time not durations
of time. The values are actually stored as a 64 bit floating point number as
an offset from 31 December 1899 00:00:00.

Time sheet applications usually record the start and end times of each
continuous work period, e.g. day or half-day. If you subtract the start time
from the end time you get a value which when formatted as a time does in fact
show the time duration, but only if less than 24 hours. Above that the
result formatted as a time would be the time less 24 hours (or multiples of
24 if the duration spans several days). If the individual differences are
each less than 24 hours, summing them and formatting the result as time will
again only give the result less 24 hours or multiples thereof.

There are a number of ways around this. You could for instance return the
difference in minutes between each start and end time using the DateDiff
function and then convert this into hours, e.g. 7 hours 30 minutes would be
represented as 7.5. These values can then easily be summed to give the total
hours worked per week.

Another way is to do the whole calculation on the basis of the underlying
values and convert that to a string showing the hours and minutes. The
following function does this:

Public Function TimeElapsed(dblTotalTime As Double) As String

Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String

'get number of hours
lngHours = Int(dblTotalTime) * HOURSINDAY + _
Format(dblTotalTime, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblTotalTime, ":nn:ss")

TimeElapsed = lngHours & strMinutesSeconds

End Function

In query for instance you could have a computed column which calls the
function:

TotalHours: TimeElapsed(Sum([EndTime] – [StartTime]))

or you could do similarly with a computed control on a form.

Ken Sheridan
Stafford, England

Northern Dave wrote:
I have a form that gives the total hours worked each day, I would like to add
all these up to give a weekly total hours it of course only displays up
24hrs, how do I display the total if it goes over 24 hours for the week

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200909/1

Hello KenSheridan,

You have answered some of my next to be posted question, I now have the
results using a simple formala
=Format(DateDiff("n",[friin],[friout])/60,".00"), this gives me the results
as 08:30-18:00 =9.5 which is aceptable as the accountants only deal in
decimal time for the pay run.

Having got the total daily hours returning in the format 9.5 how would I sum
these daily totals, or alas convert them so they can be totalled. I have
tried alsorts using trail and error, and searched the community for
inspiration but with no look, would be well pleased if you could help.



"KenSheridan via AccessMonster.com" wrote:

I might be reading too much into your post, but the fact that you raise the
question of it being unable to sum above 24 hours makes me wonder whether you
are using a date/time data type as a basis for the HoursWorked? The
date/time data type stores values representing a point in time not durations
of time. The values are actually stored as a 64 bit floating point number as
an offset from 31 December 1899 00:00:00.

Time sheet applications usually record the start and end times of each
continuous work period, e.g. day or half-day. If you subtract the start time
from the end time you get a value which when formatted as a time does in fact
show the time duration, but only if less than 24 hours. Above that the
result formatted as a time would be the time less 24 hours (or multiples of
24 if the duration spans several days). If the individual differences are
each less than 24 hours, summing them and formatting the result as time will
again only give the result less 24 hours or multiples thereof.

There are a number of ways around this. You could for instance return the
difference in minutes between each start and end time using the DateDiff
function and then convert this into hours, e.g. 7 hours 30 minutes would be
represented as 7.5. These values can then easily be summed to give the total
hours worked per week.

Another way is to do the whole calculation on the basis of the underlying
values and convert that to a string showing the hours and minutes. The
following function does this:

Public Function TimeElapsed(dblTotalTime As Double) As String

Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String

'get number of hours
lngHours = Int(dblTotalTime) * HOURSINDAY + _
Format(dblTotalTime, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblTotalTime, ":nn:ss")

TimeElapsed = lngHours & strMinutesSeconds

End Function

In query for instance you could have a computed column which calls the
function:

TotalHours: TimeElapsed(Sum([EndTime] – [StartTime]))

or you could do similarly with a computed control on a form.

Ken Sheridan
Stafford, England

Northern Dave wrote:
I have a form that gives the total hours worked each day, I would like to add
all these up to give a weekly total hours it of course only displays up
24hrs, how do I display the total if it goes over 24 hours for the week

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200909/1

If you round the values to 2 significant decimal places rather than
formatting them that will return a numeric data type rather than a string
expression, so you can then use the values in arithmetical expressions:

=Round(DateDiff("n",[friin],[friout])/60,2)

Does the fact that your fields are prefixed with 'fri' mean that you have
separate columns in the table for the start and end times of each day of the
week? If so you can't sum the values of course you'd have to add the values
of the individual expressions:

Round(DateDiff("n",[moniin],[monout])/60,2)+Round(DateDiff("n",[tuein],
[tueout])/60,2)+ etc

If this is the case it would point to a design flaw. A good design should
have one row per employee/day, with timein and timeout columns. You can then
sum rather than add the values for a week. You could have a separate
workdate column, but the best approach is to include the date in the timein
and timeout values. There is no such thing as time value per se in Access in
fact, only a date/time value. When you enter a time without a date you are
actually entering the time on 30 December 1899, Access's day-zero. You can
this for yourself if you enter the following in the debug (aka immediate)
window:

? Format(#08:30#,"dd mmmm yyyy, hh:nn:ss")

Ken Sheridan
Stafford, England

Northern Dave wrote:
Hello KenSheridan,

You have answered some of my next to be posted question, I now have the
results using a simple formala
=Format(DateDiff("n",[friin],[friout])/60,".00"), this gives me the results
as 08:30-18:00 =9.5 which is aceptable as the accountants only deal in
decimal time for the pay run.

Having got the total daily hours returning in the format 9.5 how would I sum
these daily totals, or alas convert them so they can be totalled. I have
tried alsorts using trail and error, and searched the community for
inspiration but with no look, would be well pleased if you could help.



I might be reading too much into your post, but the fact that you raise the
question of it being unable to sum above 24 hours makes me wonder whether you
[quoted text clipped - 52 lines]
all these up to give a weekly total hours it of course only displays up
24hrs, how do I display the total if it goes over 24 hours for the week

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200909/1

Hello Ken

Yet again your perception is extremely accurate, and yes I do have an in,
out & total for each day Mon-Fri. having spent some time reading the
questions and answers within this very helpful site I understand the concept
of there not being a time calculation but it being the difference between two
date times.

I have a single row in a table updated from a form so that you may pick the
week commencing date and employee name and then input the in/out with a query
calculting the Daily totals, and hopefully a weekly total. The idea is that
another record is added to the table every time an entry is made for an
employee (with the option to edit the data) on a respective week commencing.

I am now going to play with different versions of the table using your
suggestions, but it seems I may need to read some indepth literaure on either
visual Basic and/or programming Access, with your obvious knowledge would you
be kind enough to suggest any literature that you consider very good.

May I take this opportunity to thank you for your time and help with solving
my problem.



"KenSheridan via AccessMonster.com" wrote:

If you round the values to 2 significant decimal places rather than
formatting them that will return a numeric data type rather than a string
expression, so you can then use the values in arithmetical expressions:

=Round(DateDiff("n",[friin],[friout])/60,2)

Does the fact that your fields are prefixed with 'fri' mean that you have
separate columns in the table for the start and end times of each day of the
week? If so you can't sum the values of course you'd have to add the values
of the individual expressions:

Round(DateDiff("n",[moniin],[monout])/60,2)+Round(DateDiff("n",[tuein],
[tueout])/60,2)+ etc

If this is the case it would point to a design flaw. A good design should
have one row per employee/day, with timein and timeout columns. You can then
sum rather than add the values for a week. You could have a separate
workdate column, but the best approach is to include the date in the timein
and timeout values. There is no such thing as time value per se in Access in
fact, only a date/time value. When you enter a time without a date you are
actually entering the time on 30 December 1899, Access's day-zero. You can
this for yourself if you enter the following in the debug (aka immediate)
window:

? Format(#08:30#,"dd mmmm yyyy, hh:nn:ss")

Ken Sheridan
Stafford, England

Northern Dave wrote:
Hello KenSheridan,

You have answered some of my next to be posted question, I now have the
results using a simple formala
=Format(DateDiff("n",[friin],[friout])/60,".00"), this gives me the results
as 08:30-18:00 =9.5 which is aceptable as the accountants only deal in
decimal time for the pay run.

Having got the total daily hours returning in the format 9.5 how would I sum
these daily totals, or alas convert them so they can be totalled. I have
tried alsorts using trail and error, and searched the community for
inspiration but with no look, would be well pleased if you could help.



I might be reading too much into your post, but the fact that you raise the
question of it being unable to sum above 24 hours makes me wonder whether you
[quoted text clipped - 52 lines]
all these up to give a weekly total hours it of course only displays up
24hrs, how do I display the total if it goes over 24 hours for the week

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200909/1

A good starting point would be Crystal's introductory tutorial at:

http://www.accessmvp.com/Strive4Peace/

It does what it says on the tin.

Ken Sheridan
Stafford, England

Northern Dave wrote:
Hello Ken

Yet again your perception is extremely accurate, and yes I do have an in,
out & total for each day Mon-Fri. having spent some time reading the
questions and answers within this very helpful site I understand the concept
of there not being a time calculation but it being the difference between two
date times.

I have a single row in a table updated from a form so that you may pick the
week commencing date and employee name and then input the in/out with a query
calculting the Daily totals, and hopefully a weekly total. The idea is that
another record is added to the table every time an entry is made for an
employee (with the option to edit the data) on a respective week commencing.

I am now going to play with different versions of the table using your
suggestions, but it seems I may need to read some indepth literaure on either
visual Basic and/or programming Access, with your obvious knowledge would you
be kind enough to suggest any literature that you consider very good.

May I take this opportunity to thank you for your time and help with solving
my problem.

If you round the values to 2 significant decimal places rather than
formatting them that will return a numeric data type rather than a string
[quoted text clipped - 43 lines]
all these up to give a weekly total hours it of course only displays up
24hrs, how do I display the total if it goes over 24 hours for the week

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200910/1

I have implemented the ideas that you suggested, and now have a form that
calculates the daily, weekly and overtime hours for a set of employees. My
next move is obviously to be able to relate this information to a specific
week date, and thus allowing this information to be retrieved for display or
printing against each individual by week.

Before setting out to expand the functionality of the database I have taken
up your suggestion of reading Crystal's Introductory Tutorial, and I am
finding it excellent.
Thankyou again for pointing me in the right direction, you deserve many gold
stars.


"KenSheridan via AccessMonster.com" wrote:

A good starting point would be Crystal's introductory tutorial at:

http://www.accessmvp.com/Strive4Peace/

It does what it says on the tin.

Ken Sheridan
Stafford, England

Northern Dave wrote:
Hello Ken

Yet again your perception is extremely accurate, and yes I do have an in,
out & total for each day Mon-Fri. having spent some time reading the
questions and answers within this very helpful site I understand the concept
of there not being a time calculation but it being the difference between two
date times.

I have a single row in a table updated from a form so that you may pick the
week commencing date and employee name and then input the in/out with a query
calculting the Daily totals, and hopefully a weekly total. The idea is that
another record is added to the table every time an entry is made for an
employee (with the option to edit the data) on a respective week commencing.

I am now going to play with different versions of the table using your
suggestions, but it seems I may need to read some indepth literaure on either
visual Basic and/or programming Access, with your obvious knowledge would you
be kind enough to suggest any literature that you consider very good.

May I take this opportunity to thank you for your time and help with solving
my problem.

If you round the values to 2 significant decimal places rather than
formatting them that will return a numeric data type rather than a string
[quoted text clipped - 43 lines]
all these up to give a weekly total hours it of course only displays up
24hrs, how do I display the total if it goes over 24 hours for the week

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200910/1

As I mentioned earlier you can either have a separate WorkDate column in the
table as well as StartTime and EndTime columns, in which case you'd enter the
date in the first and the time values only in the latter two; or you can
enter the start and end times as the full date/time value. If you do the
former you can just add the WorkDate to the StartTime and EndTime values to
give the full date/time value for each.

Most experienced developers would probably favour the second option, having
just the StartTime and EndTime columns, but either way would work.

To group the dates by week you could use the DatePart function to return the
week number, which for some purposes will be appropriate, but bear in mind
with data over a number of years the year will have to be included in the
grouping to differentiate between the same week numbers in different years.

Another way, which for timesheet purposes is how its usually been done in
organisations in which I've worked, is to define a week by its 'week-
starting' date, usually the Sunday or Monday. This can easily be computed
from any date, but by writing a little function to do it you can at any time
determine the 'week-starting' date by calling the function and passing the
date into it. The following would do this, and also allows you to select the
day of the week at which a week is considered to start:

Public Function WeekStarting(dtmdate As Date, intStartDay As Integer) As Date

WeekStarting = dtmdate - Weekday(dtmdate, intStartDay) + 1

End Function

So for today (2 October 2009) to return the 'week-starting' date with Sunday
as the start day of the week:

WeekStarting(#2009-10-02#,1)

would return 27 September 2009, and with Monday as the start day of the week,

WeekStarting(#2009-10-02#,2)

would return 28 September 2009.

I've used the ISO standard for date notation of YYYY-MM-DD to avoid ambiguity
BTW. The US format of MM/DD/YYYY could also be used, but not the UK format
of DD/MM/YYYY (which would turn 4 July into 7 April!). Normally you wouldn't
need to worry about this of course, as the column name would be used, e.g.

WeekStarting([WorkDate],2)

Ken Sheridan
Stafford, England

Northern Dave wrote:
I have implemented the ideas that you suggested, and now have a form that
calculates the daily, weekly and overtime hours for a set of employees. My
next move is obviously to be able to relate this information to a specific
week date, and thus allowing this information to be retrieved for display or
printing against each individual by week.

Before setting out to expand the functionality of the database I have taken
up your suggestion of reading Crystal's Introductory Tutorial, and I am
finding it excellent.
Thankyou again for pointing me in the right direction, you deserve many gold
stars.

A good starting point would be Crystal's introductory tutorial at:

[quoted text clipped - 32 lines]
all these up to give a weekly total hours it of course only displays up
24hrs, how do I display the total if it goes over 24 hours for the week

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200910/1