Time Sheet-Access 97

Topic

New Reply

I’m working on a payroll program for which I’ve also created a time sheet – due to hourly workers. It contains such
fields as: StartTime, EndTime, LunchBreak and NetTime (which obviously should be calculated)

Another problem is that I’m using marine time:

08:30 – 17:00 which calcs out to 7.30

but need to get this to 7.50 instead of 7.30 to use it to calculate pay, i.e., 7.50 x $16.00.

Also, I’ve used

Hours: Format([StartTime]-1-[EndTime],”Short Time”)

to calculate the difference between starting and ending times but can’t get lunch break to work with it in the query (because Hours is calculated). I tried moving it out of the query and into the report but I still get #ERR messages.

Seems like it should be very easy to do this but it just doesn’t want to work for me.

Any suggestions

Thanks

“CD”

Reply | Quote

Replies

In Access dates and times are actually stored as numbers.
For instance:

1/11/01 5:00 pm is stored as 36902.708
1/11/01 10:30 pm is 36902.94

The integer portion is the day and the decimal portion is the time. You can convert the dates/times to numbers subtract them, multiply by 24 and have the difference in hours:

(cdbl(1/11/01 10:00 pm ) – cdbl(1/11/01 5:00 pm ))*24 = 5.5

Reply | Quote

this formula worked for me
Text126 = (((((((Format(T_OUT, “hh”) * 60) + Format(T_OUT, “nn”)) * 60) + Format(T_OUT, “SS”)) – ((((Format(T_IN, “hh”) * 60) + Format(T_IN, “nn”)) * 60) + Format(T_IN, “SS”)))) / 60 ) / 60
hope this helps
JerryC

Reply | Quote

Hi CD,
Can you explain further about your problem with lunch breaks – I didn’t quite follow it. Thanks.

Reply | Quote

Hi, Rory!

The problem is I show:

[StartTime[ 08:00 [EndTime] 16:30 which workds out to 8.30 hours. However, if I show [LunchBreak] .50 (1/2 hour) it obviously won’t calculate properly so I would have to show lunch break as .30 – but then what do I do when lunch breark is 45 minutes? Hourly wage is $16.00.

So, what I need to do is have a calculation that deducts ending time from starting time, less lunch break to arrive at regular hours which I then multiply by $16.00 or whatever the rate is. At this point if I have Hours: Format([StartTime]-1-[EndTime],”Short Time”) in the query to get Hours but I can’t use =[Hours]-[LunchBreak] in the query and when I attempt to do that in the report, it also errs out. So what I’ve been doing is just entering the number of hours in a field instead of letting the computer do it and then let that calculate against the rate.

Does this explain (and can you help?)

Thanks

“CD”

Reply | Quote

Try this:

Salary: Format(((CDbl([endtime])-CDbl([starttime]))*24-([Lunch]/60))*16,”$#.##”)

Where lunch is enter in minutes

Reply | Quote

Thanks for all the help; I’ll give it a try.

“CD”

Reply | Quote

Hi CD,
cpod’s posting will work perfectly (I think, didn’t actually test it) but it might be useful to know that you can also do it this way:
HrsWorked: DateDiff(“h”,[start_time],[end_time])-[lunch_break]*[hourly_rate]
where I’m assuming that lunch_break is in hours (e.g. 0.5).
If you want to see all the possibilities of DateDiff (very useful) the help is much better if you look it up in the VB editor environment rather than from a database window.
Hope that helps.

Reply | Quote

Hi Rory, actually the both worked very well. Thanks to both of you.

“CD”

Reply | Quote