Re: Calculating Time Sheet

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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/Forums.aspx/access-gettingstarted/200909/1

.

Relevant Pages

  • Re: Calculating Time Sheet
    ... so you can then use the values in arithmetical expressions: ... If so you can't sum the values of course you'd have to add the values ... these daily totals, or alas convert them so they can be totalled. ...
    (microsoft.public.access.gettingstarted)
  • RE: dsum
    ... > The display however is just numbers and loses the currrency formatting. ... or an alternative method to sum the totals and get correct formatting? ...
    (microsoft.public.access.forms)
  • Re: Round as calculator
    ... I have used the following round on every expression ... expressions and not all? ... >> match a calculator. ... >> calculate the totals using software and the other entity ...
    (microsoft.public.access.queries)
  • Re: Round as calculator
    ... Why are you making expressions more complex than necessary? ... author "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" ... >>> match a calculator. ... As I>>> calculate the totals using software and the other entity ...
    (microsoft.public.access.queries)
  • Re: Sum of calculated fields
    ... leading you astray. ... >unnessasary parts of the expressions, cleaned up, and now it definately works ... >Marshall Barton wrote: ... >>that Sum is doing in there. ...
    (microsoft.public.access.formscoding)