Access Time Calculation

  • Thread starter Thread starter Difficult1
  • Start date Start date
D

Difficult1

Good morning. I am in the process of designing an Access database that will
track in/out times. It is sort of a special circumstance calculation that I
need. What I am looking for is a formula (or some sort of code) that will
allow me to calculate the difference between times, with some specific "ifs"
involved. First off, I want to enter TimeIn and TimeOut (whatever their names
would be) and have each of them rounded to the nearest quarter hour (ie
7:38AM would be 7:45 rounded). Then, I need to take anything between 7:30 and
8:00 (both AM) and calculate the amount of time there. Then, I need to take
12noon (absolute) and the end time (again, rounded to the nearest quarter
hour) and calculate that amount of time. I then need to add the results to
get the total time between those hours.

Anybody got suggestions? I know it is pretty particular. This is to
calculate time for daycare services before school (7:30-8) and after school
(12PM-3:30PM).
Any help would be greatly appreciated. I have tried a couple of different
things that seem way to complex to be right, although most of it works out, I
cannot simply add the AM total and the PM totals together to get the grand
total.
 
Hi,

For the daycare time in hours try:

DaycareHours:
DateDiff("n",TimeSerial(Hour([TimeIn]),((Minute([TimeIn])+7.5)\15)*15,0),TimeSerial(Hour([TimeOut]),((Minute([TimeOut])+7.5)\15)*15,0))/60-4

For minutes try:

DaycareMinutes:
DateDiff("n",TimeSerial(Hour([TimeIn]),((Minute([TimeIn])+7.5)\15)*15,0),TimeSerial(Hour([TimeOut]),((Minute([TimeOut])+7.5)\15)*15,0))-4*60

Clifford Bass
 
Thanks for getting back so quickly. Where do I put this?

Clifford Bass said:
Hi,

For the daycare time in hours try:

DaycareHours:
DateDiff("n",TimeSerial(Hour([TimeIn]),((Minute([TimeIn])+7.5)\15)*15,0),TimeSerial(Hour([TimeOut]),((Minute([TimeOut])+7.5)\15)*15,0))/60-4

For minutes try:

DaycareMinutes:
DateDiff("n",TimeSerial(Hour([TimeIn]),((Minute([TimeIn])+7.5)\15)*15,0),TimeSerial(Hour([TimeOut]),((Minute([TimeOut])+7.5)\15)*15,0))-4*60

Clifford Bass

Difficult1 said:
Good morning. I am in the process of designing an Access database that will
track in/out times. It is sort of a special circumstance calculation that I
need. What I am looking for is a formula (or some sort of code) that will
allow me to calculate the difference between times, with some specific "ifs"
involved. First off, I want to enter TimeIn and TimeOut (whatever their names
would be) and have each of them rounded to the nearest quarter hour (ie
7:38AM would be 7:45 rounded). Then, I need to take anything between 7:30 and
8:00 (both AM) and calculate the amount of time there. Then, I need to take
12noon (absolute) and the end time (again, rounded to the nearest quarter
hour) and calculate that amount of time. I then need to add the results to
get the total time between those hours.

Anybody got suggestions? I know it is pretty particular. This is to
calculate time for daycare services before school (7:30-8) and after school
(12PM-3:30PM).
Any help would be greatly appreciated. I have tried a couple of different
things that seem way to complex to be right, although most of it works out, I
cannot simply add the AM total and the PM totals together to get the grand
total.
Click to expand...
Click to expand...
 
Hi,

That depends on where you want to use it. You could place it in a
query by simply copying the entire line (one line even though wrapped by the
discussion group viewer) into the top line of your query's field list grid.
You could then use that value in a report. If you want it to show on a form,
as a calculated field, you would create an unbound text box and set its
Control Source to an equals sign followed by the stuff after the colon.

=DateDiff(....

If that does not help, please be specific as to where you want to
use/show the caculation.

Clifford Bass
 
You are so awesome! That seems to have done the trick. I'll go and test it a
bit more to make sure it works with all of my scenarios and see what happens!
 
Hi,

Good to hear that it is working so far. Glad to help. Good luck with
the testing.

Clifford Bass
 
Hi,

Works great, unless someone drops there kid off after 8. We don't charge for
8-12, but the kids are there. Parents can drop off at anytime.. sometimes
they are late getting there... 8-12 is a PreK program that is free. It is the
Day Care before and after that program that count. we keep a time sheet that
says what time the kid gets there and leaves. I was hoping to punch in those
numbers and have it calculate manually. I have figured a workaround in Excel,
but, the report that prints (export to Crystal) is inadequate. any other
thoughts?
 
Hi,

Okay, then for the purposes of the calculation, we will deal with those
who arrive late as if they arrived at 8:00 am and those who leave early as if
they left at 12:00 noon. Try:

DaycareHours:
DateDiff("n",IIf(Hour([TimeIn])<8,TimeSerial(Hour([TimeIn]),((Minute([TimeIn])+7.5)\15)*15,0),#8:00:00
AM#),IIf(Hour([TimeOut])>=12,TimeSerial(Hour([TimeOut]),((Minute([TimeOut])+7.5)\15)*15,0),#12:00:00 PM#))/60-4

Just in case... this does of course assume that no one arrives before
the prior midnight and that no one leaves after the following midnight.

Clifford Bass
 
You are a genius! This works absolutely perfect! Thank you so much!

Clifford Bass said:
Hi,

Okay, then for the purposes of the calculation, we will deal with those
who arrive late as if they arrived at 8:00 am and those who leave early as if
they left at 12:00 noon. Try:

DaycareHours:
DateDiff("n",IIf(Hour([TimeIn])<8,TimeSerial(Hour([TimeIn]),((Minute([TimeIn])+7.5)\15)*15,0),#8:00:00
AM#),IIf(Hour([TimeOut])>=12,TimeSerial(Hour([TimeOut]),((Minute([TimeOut])+7.5)\15)*15,0),#12:00:00 PM#))/60-4

Just in case... this does of course assume that no one arrives before
the prior midnight and that no one leaves after the following midnight.

Clifford Bass

Difficult1 said:
Hi,

Works great, unless someone drops there kid off after 8. We don't charge for
8-12, but the kids are there. Parents can drop off at anytime.. sometimes
they are late getting there... 8-12 is a PreK program that is free. It is the
Day Care before and after that program that count. we keep a time sheet that
says what time the kid gets there and leaves. I was hoping to punch in those
numbers and have it calculate manually. I have figured a workaround in Excel,
but, the report that prints (export to Crystal) is inadequate. any other
thoughts?
Click to expand...
Click to expand...
 
Back
Top