Help with age calculation

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

Guest

I have a peculiar problem in that I need to calculate the age of certain cases. This is not hard, except that the age process is halted sometimes when the day clock is stopped (for further investigation). I have no problem calculating the age when the day clock resumes but I can't get the stopped clock matters to stop aging. I'm currently using a Base Age: DateDiff("d",[Date Received in Legal],Date()) and subtracting the time spent in suspended status, but this isn't adequate. ??
 
Dear Melissa:

It sounds like you are saying you need the total accumulated time for
a case from its inception to the current time, except that there may
be one (or more?) periods which are not counted because the "clock is
stopped." For each period when the clock is stopped, you would have a
deduction. You need to pick a resolution for this.

case inception 1/1/04 12:00
current time 7/10/04 14:00

Stop1 1/13/04 15:00 to 1/13/04 17:30
Stop2 2/11/04 08:00 to 4/18/04 16:10

The first stop is for 0 days, and if you do not resolve it any
further, it will disappear. If you resolve it down to minutes, for
example, it is 150 minutes. If you resolve everything to minutes,
then add up the Stop time and subtract that from the overall time, you
can then convert that to days, dividing by 24 * 60. That would be the
number of days. Resolving time to minutes may give a different
answer. What you want should determine what you do with this.

I'm not sure what isn't adequate about what you're doing now, but I
was suspecting it might be related to the above. Or perhaps you are
not even recording time of day, only dates. If you give a bit more
detail, perhaps I can help.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hello again, and thanks for your message.

My problem is that I'm working from an earlier simple calculation in a Query, where the "Time Out" time was calculated by the date difference between Time Out &Time Back In, which was then deducted from the base age. Now my bosses want the cases which are still in "Time Out" to appear, with the age at the time they went "out." Of course this time as currently calculated ends up as zero (because there's no "Time Back In" date) which doesn't do me any good.

I'd have to get the calculation to read the "Time Back In" date as either the entered date OR the current date, and I have no idea how to do that. Or there's probably some other way. Or not.

Thanks, Melissa

Tom Ellison said:
Dear Melissa:

It sounds like you are saying you need the total accumulated time for
a case from its inception to the current time, except that there may
be one (or more?) periods which are not counted because the "clock is
stopped." For each period when the clock is stopped, you would have a
deduction. You need to pick a resolution for this.

case inception 1/1/04 12:00
current time 7/10/04 14:00

Stop1 1/13/04 15:00 to 1/13/04 17:30
Stop2 2/11/04 08:00 to 4/18/04 16:10

The first stop is for 0 days, and if you do not resolve it any
further, it will disappear. If you resolve it down to minutes, for
example, it is 150 minutes. If you resolve everything to minutes,
then add up the Stop time and subtract that from the overall time, you
can then convert that to days, dividing by 24 * 60. That would be the
number of days. Resolving time to minutes may give a different
answer. What you want should determine what you do with this.

I'm not sure what isn't adequate about what you're doing now, but I
was suspecting it might be related to the above. Or perhaps you are
not even recording time of day, only dates. If you give a bit more
detail, perhaps I can help.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have a peculiar problem in that I need to calculate the age of certain cases. This is not hard, except that the age process is halted sometimes when the day clock is stopped (for further investigation). I have no problem calculating the age when the day clock resumes but I can't get the stopped clock matters to stop aging. I'm currently using a Base Age: DateDiff("d",[Date Received in Legal],Date()) and subtracting the time spent in suspended status, but this isn't adequate. ??
Click to expand...
Click to expand...
 
Back
Top