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

"Difficult1" wrote:

> 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.

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

"Clifford Bass" wrote:

> 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" wrote:
>
> > 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,

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

"Difficult1" wrote:

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

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!

"Clifford Bass" wrote:

> 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
>
> "Difficult1" wrote:
>
> > Thanks for getting back so quickly. Where do I put this?

Hi,

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

Clifford Bass

"Difficult1" wrote:

> 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,

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?

"Clifford Bass" wrote:

> Hi,
>
> Good to hear that it is working so far. Glad to help. Good luck with
> the testing.
>
> Clifford Bass
>
> "Difficult1" wrote:
>
> > 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,

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" wrote:

> 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?

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

"Clifford Bass" wrote:

> 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" wrote:
>
> > 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,

Good to hear it is working as desired. Your are quite welcome!

Clifford Bass

"Difficult1" wrote:

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