Calculating Time (Like in a Timesheet)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Okay I was reviewing all the posts from other folks and I couldn't find the
answer to the problem that I am facing. Someone please help me...

I have two time fields "Start Time" and a "End Time".

I need to calculate these two fields but also accounting another field that
is label'd "Less Lunch"

Example: Wayne comes into work at 7:30am and works until 4:30pm but has an
hour lunch. On his timesheet, there is a place for him to put his start
time, his end time and his "Less Lunch" information.

Can I make the database account for that "Less Lunch"?

I already have this formula in the works...
=Format([DayOneMonStartTime]-1-[DayOneMonFinishTime],"Short Time")
But as you can see, it isn't accounting for that "Less Lunch".

How would I rewrite this formula?

I hope I didn't confuse you guys but this thing has frusterated me all day.
If anyone can help me out, it would greatly be appreciated.

Thanks a million...

Leonard Peacock
 
Leonard,

I believe that Microsoft Access stores dates & times as serial numbers,
with 1 being a whole day, 0.5 being midday etc. Therefore your problem
should easily be solved using the following:

=Format(([DayOneMonFinishTime]-[DayOneMonStartTime])-[LessLunch],"Short
Time")

However, you may run into problems if a shift runs over two days, ie 6pm
start - 2 am finish

Is this likely to be the case, or will what I have already suggested solve
your problem?

John Webb
 
i think it should be + [less lunch] because he used -1 in his original
formula.
According to your explanation (i checked it, and it is correct, but the
formula isn't) he should do the following.

e.g.
?format(#17:12#-#08:12#,"hh:mm")
==> 9:00

?format(#17:12#-#08:12#-(1/24),"hh:mm")
==> 8:00

?format(#17:12#-#08:12#-(1/24)+([less time]/24),"hh:mm")
==> 8:00 + some time they didn't used during the break.

If [less time] is giving in hours...
 
i think it should be + [less lunch] because he used -1 in his original
formula.
According to your explanation (i checked it, and it is correct, but the
formula isn't) he should do the following.

......

my formula works as I said it works Damian, but it has its limitations as
originally stated. If you examine my formula it was

(End Time - Start Time) - Less Lunch

so, for somebody doing 8 hours (9-5) with 1 hour lunch break it would be:

(17:00 - 09:00) - 01:00
(0.7083 - 0.375) - 0.0416
*approximate values used

which equals 7 hours, which is exactly as it should.
If you added the lunch time it would equal 9, which is incorrect.

Cheers

John Webb
 
Dear Damiaan,

Thanks for the help. I tried inputting your formula without any luck.
After placing a "1" in the "Less Lunch" field, the hours remain at 9 hours
instead of droping it down by one to "8". Recommend anything else?

Thanks so much for your time and help with this. I really appreciate it!! :-)

Sincerely,

Leonard W. Peacock

Damiaan said:
=Format([DayOneMonStartTime]-1-[DayOneMonFinishTime]+[Less
Lunch],"Short Time")

--

Kind Regards
Damiaan
info @t dampee d0t be


Leonard Peacock said:
Okay I was reviewing all the posts from other folks and I couldn't find
the
answer to the problem that I am facing. Someone please help me...

I have two time fields "Start Time" and a "End Time".

I need to calculate these two fields but also accounting another field
that
is label'd "Less Lunch"

Example: Wayne comes into work at 7:30am and works until 4:30pm but has
an
hour lunch. On his timesheet, there is a place for him to put his start
time, his end time and his "Less Lunch" information.

Can I make the database account for that "Less Lunch"?

I already have this formula in the works...
=Format([DayOneMonStartTime]-1-[DayOneMonFinishTime],"Short Time")
But as you can see, it isn't accounting for that "Less Lunch".

How would I rewrite this formula?

I hope I didn't confuse you guys but this thing has frusterated me all
day.
If anyone can help me out, it would greatly be appreciated.

Thanks a million...

Leonard Peacock
Click to expand...
Click to expand...
 
Good Morning Mr. Webb,

I really do appreciate your help with this. Our shifts never run past
4:30pm really. My timesheet typically will read for one day, for example:

Day Date Time In Time Out Less Lunch Daily
Total
Mon. 3/21/2005 7:30 AM 4:30 PM 1 8

The data entry form that I built basically mimics our timesheets.

So in the database, I have all the information inputted in exactly how I
just typed it above, however, instead of the Total Hours Worked equaling "8",
before I started playing around with it, the numbers didn't change, but when
I implemented all yall's recommendations, at least the Daily Total changed.

Your recommendation makes the Total Hours worked equal to "15" if I have a
"1" entered in the Less Lunch field.

Do you have any other recommendations? :-)

Again I do appreciate your help!!

Sincerely,

Leonard W. Peacock
 
Leonard,

Bare in mind that Access stores time / date fields as serial numbers, and
that

1 day = 1
1 hour = 1/24

Therefore, a simple change to my formula (to convert the 1 hour into a time
serial that Access uses) should do the trick:

=Format(([DayOneMonFinishTime]-[DayOneMonStartTime])-([LessLunch]/24)
,"Short Time")

I can forsee another possible problem here thou, and that would be to make
sure that the data type you store the LESS LUNCH field in, will support
decimal numbers.
The reason I say this is because people may decide to only take half hour
lunch break, and if the data type will only hold integers then it will show
as having no lunch.

Otherwise, what I have amended should work.

Hope that helps

John Webb
 
Just thought of an alternative that may be better for you anyway.

I imagine you want to add up the hours over a week / month / year etc. If
so, you would be better to store the hours per day as a number rather than
a time (although they are one and the same really).

To do this, use this formula:

=(([DayOneMonFinishTime]-[DayOneMonStartTime])*24) -([LessLunch])

Otherwise, when you add anything over 24 hours, the "clock" resets but the
"day counter" is incremented.

What I mean by this is the timesheet would look like:

Day Hours Cumulative Hours
1 8 08:00
2 8 16:00
3 8 00:00
4 8 08:00
5 8 16:00

You wouldn't see it, but on day 3 the date would have moved forward one day.

It's kind of difficult to explain, but if you plan on doing any sort of
summing on total hours worked, I would switch to using the formula in this
post.

Cheers

John webb
 
Mr. Webb,

Your formula worked out great! Thank you so much for your help in this.

Where did you learn all this? :-) I need to follow in the same footsteps! :-)

Sincerely,

Leonard W. Peacock
 
Mr. Webb,

Again, thanks for your help to me. I really do appreciate it. I have
another question though.

I took your advice and used the second example you suggested, which is below.

For each day, these DAILY TOTALs, have their own text box (7 total). These
text boxes are labeled: TEXT115, TEXT117, TEXT118, TEXT119, TEXT220, TEXT221
and TEXT222. TEXT221 and TEXT222 does not have anything displayed because I
don't work Saturdays and Sundays, so naturally, I don't enter in hours.

When I attempt to add all 7 text boxes, it doesn't display "40" like it
should because I work 8 hours a day for five days.

If I go ahead and enter in times for Saturday and Sunday, as if I worked, it
will then total up all the text boxes. So basically, if I don't work a day
and one of these text boxes remains empty, the database will not add up the
hours worked in this week.

Any suggestions?

Thanks again!!

Leonard W. Peacock
 
Hi Leonard,

Sorry I took so long to get back to you - unfortunately answering queries
on this site is not my day job - would be great if it was.

With reference to where I learnt all this, pretty much just playing around
and reading some books. Forums such as this one are great too, as you get
a nice mix of people who are having the same problems as you are, and
people that have had and dealt with those problems.

That said, I think the fact that I've been 'playing' with Microsoft Access
since version 2 might have helped :o)

Right, down to business:

I should have thought about the problem you have encountered previously.
Basically, the 2 textboxes which contain no data actually contain Null
values.

Microsoft Access doesn't like adding Null values (I think), but if in place
of the additions you use the SUM formula for the totals, I believe you
should get the result you expect. I believe the same can be said for
Microsoft Excel in this matter.

However, you could avoid having Nulls in the first place, which I would
suggest, as it will make it extremly clear to your users that no hours have
been worked, (as if it could be any clearer).

To get rid of the Null values, you need to modify your formula to include
the Nz function, as follows:

=Nz((([DayOneMonFinishTime]-[DayOneMonStartTime])*24) -([LessLunch]),0)

Basically the Nz function will return the result of the first argument
passed to it - in this case your main function, but if a Null value is
returned it will return the second argument - in this case zero. Then you
should have no problems adding up the total hours worked.

Let me know if this still doesn't get the result you expect, and I'll build
a test database and have a play - but I'm fairly certain it will work.

Kind Regards

John Webb
 
If I go ahead and enter in times for Saturday and Sunday, as if I worked, it
will then total up all the text boxes. So basically, if I don't work a day
and one of these text boxes remains empty, the database will not add up the
hours worked in this week.
Click to expand...

A NULL value in any of the textboxes will cause the entire expression
to be NULL. The solution is to use the NZ() function to convert Null
to Zero - e.g.

NZ(txtMonday) + NZ(txtTuesday) + NZ(txtWednesday) ...

(and yes, I'm suggesting you rename your text boxes to something
meaningful!)

John W. Vinson[MVP]
 
Mr. Webb,

Again you solved my problem!! Your formula worked out very well. Thanks so
much for your time and help.

I am starting to take notes of the problems I run into and then the
solution. I hate reading books so I don't read, if I can help it but
computer books and things like that (Access for example) I will skim over
them. I need to get the ball rolling and start reading huh?

Anyway, thanks again and you have a great night!!

Leonard W. Peacock
 
There is a ready made function in Access that helps called DateDiff.

TimeWorked = (DateDiff("n", [StartTime], [EndTime]) / 60) - [Lunch]

The above calculates the time difference in minutes (you can do it hours
but it will only calculate whole hours) divided by 60 to convert to hours,
and then take off the lunch break in hours.

As always there is always more than one way to do anything, you pay your
money and take your choice.
 
Back
Top