Time calculation

[Fluke]

Hi

I understand variations of this question have been asked numerous times, and
I've read them, but none seem to be exactly what I'm after.

I need to calculate hours worked between a start time and an end time.
Seems simple. Then the hours for each day are added for the weekly total.
I've tried a few different options such as...

Sun1:
IIf(IsNull([Name1]),"",Format(([tblTimesheet2]![N1SunOut]-[tblTimesheet2]![N1SunIn])*24,"##.0"))

Or

Mon1:
IIf(IsNull([Name1]),"",DateDiff("n",[tblTimesheet2]![N1MonIn],[tblTimesheet2]![N1MonOut])/60-0.5)

The second one has the -0.5 because, during the week, half an hour is taken
off for lunch. I seemed to have it working so, during the week, if the start
time is 08:30 and the end time is 16:30, it gives 7.5 hours worked. And a
weekend would give 8. But I put in, for the Sunday, 10:45 start and 15:15
end, expecting 4.5 and it gave 5. And when i put that someone started at
10:20 one day, it threw it completely.

Can anyone help please?

 

[Dale Fye]

You are making this soooooo... much harder than it has to be by having
individual columns for each day. Your data structure looks like it is right
out of a spreadsheet, which is just wrong for use in a relational database.
What you need is another table for EmployeeHours, with fields EmployeeID,
DateWorked, TimeIn, and TimeOut.

However given the structure you have, either one of the two methods you
describe should work. I plugged a variety of times into both of those
functions, and they both worked fine.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[John Spencer]

IIf(IsNull([Name1]),Null,
DateDiff("n",[tblTimesheet2]![N1MonIn],[tblTimesheet2]![N1MonOut])/60-0.5)

Use the same formula for Saturday and Sunday but don't subtract the half hour
out. DateTime math should be done using the DateDiff function.

Also don't return "" a zero-length string, return Null. If you return "" as
an option then the IIF will return a STRING with number characters for the
second part. This can lead to unexpected results. Format also returns a
string for your number.

If by some chance all your values got typed as strings, the use of the +
operator would concatenate the strings instead of adding them.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Fluke]

Hi Dale.

I understand what you're saying, but I couldn't help it. It used to be just
hours worked each day but someone was found to be fiddling, so clocking in
and out hours have had to be added along with totals, so it's inheriting the
previous structure.

I can't understand what's happening then, cos it's giving me 5 hours uinstad
of 4.5.

You are making this soooooo... much harder than it has to be by having
individual columns for each day. Your data structure looks like it is right
out of a spreadsheet, which is just wrong for use in a relational database.
What you need is another table for EmployeeHours, with fields EmployeeID,
DateWorked, TimeIn, and TimeOut.

However given the structure you have, either one of the two methods you
describe should work. I plugged a variety of times into both of those
functions, and they both worked fine.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hi

I understand variations of this question have been asked numerous times, and
I've read them, but none seem to be exactly what I'm after.

I need to calculate hours worked between a start time and an end time.
Seems simple. Then the hours for each day are added for the weekly total.
I've tried a few different options such as...

Sun1:
IIf(IsNull([Name1]),"",Format(([tblTimesheet2]![N1SunOut]-[tblTimesheet2]![N1SunIn])*24,"##.0"))

Or

Mon1:
IIf(IsNull([Name1]),"",DateDiff("n",[tblTimesheet2]![N1MonIn],[tblTimesheet2]![N1MonOut])/60-0.5)

The second one has the -0.5 because, during the week, half an hour is taken
off for lunch. I seemed to have it working so, during the week, if the start
time is 08:30 and the end time is 16:30, it gives 7.5 hours worked. And a
weekend would give 8. But I put in, for the Sunday, 10:45 start and 15:15
end, expecting 4.5 and it gave 5. And when i put that someone started at
10:20 one day, it threw it completely.

Can anyone help please?

 

[Fluke]

Thanks John. That worked perfectly. It was the " that, for some reason, was
throwing it off. Replaced them with Null and it worked spot on. Thanks a
million!

IIf(IsNull([Name1]),Null,
DateDiff("n",[tblTimesheet2]![N1MonIn],[tblTimesheet2]![N1MonOut])/60-0.5)

Use the same formula for Saturday and Sunday but don't subtract the half hour
out. DateTime math should be done using the DateDiff function.

Also don't return "" a zero-length string, return Null. If you return "" as
an option then the IIF will return a STRING with number characters for the
second part. This can lead to unexpected results. Format also returns a
string for your number.

If by some chance all your values got typed as strings, the use of the +
operator would concatenate the strings instead of adding them.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi

I understand variations of this question have been asked numerous times, and
I've read them, but none seem to be exactly what I'm after.

I need to calculate hours worked between a start time and an end time.
Seems simple. Then the hours for each day are added for the weekly total.
I've tried a few different options such as...

Sun1:
IIf(IsNull([Name1]),"",Format(([tblTimesheet2]![N1SunOut]-[tblTimesheet2]![N1SunIn])*24,"##.0"))

Or

Mon1:
IIf(IsNull([Name1]),"",DateDiff("n",[tblTimesheet2]![N1MonIn],[tblTimesheet2]![N1MonOut])/60-0.5)

The second one has the -0.5 because, during the week, half an hour is taken
off for lunch. I seemed to have it working so, during the week, if the start
time is 08:30 and the end time is 16:30, it gives 7.5 hours worked. And a
weekend would give 8. But I put in, for the Sunday, 10:45 start and 15:15
end, expecting 4.5 and it gave 5. And when i put that someone started at
10:20 one day, it threw it completely.

Can anyone help please?