Elapsed Time

  • Thread starter Thread starter Jan
  • Start date Start date
J

Jan

I have a table containing dates internal audits were
performed [auditdate] and dates when the audits were
closed [auditclose]. I want to compute the elapsed time
between those two dates in a new field [elapsedtime] and
add another field that says if "less than 30 days"
or "more than 30 days". Can anyone help me?
 
In addition to the criteria below, some of the records
don't have closed dates, but we still want to compute
elapsed time and "more" or "less" than 30 days. Thanks.
 
Jan

One approach would be to add an IIF() statement into a new field in your
query. The general approach is something like:

"If date1-date2 is less than (?or equal to) 30, write "less than",
otherwise, write "more than"."
 
Try using this formula in your query:
NewFieldName: DateDiff("d", [auditdate], [auditclose])

Then set up a Yes/No field to capture date differences
more than 30 days old (or less than).

For example:
OverDays: (If((NewFieldName => 30), yes))

The "d" in the DateDiff formula stands for days.
 
I just replied to this Jan. I forgot the formula is IIf
(), not If() for the second formula that I gave you.
 
Jan said:
I have a table containing dates internal audits were
performed [auditdate] and dates when the audits were
closed [auditclose]. I want to compute the elapsed time
between those two dates in a new field [elapsedtime] and
add another field that says if "less than 30 days"
or "more than 30 days". Can anyone help me?
Click to expand...


Should be something like this (Note: Untested):

SELECT M1.AuditDate
,M1.AuditClose
,DateDiff("d", M1.AuditDate, Nz(M1.AuditClose, Date())) As AuditPeriod
,SWITCH(DateDiff("d", M1.AuditDate, Nz(M1.AuditClose, Date())) < 30,
"Less than 30 Days",
DateDiff("d", M1.AuditDate, Nz(M1.AuditClose, Date())) > 30,
"More than 30 Days")
AS RemainingTime
FROM MyTable AS M1

Note: If AuditClose is Null, then today's date is substituted. If
something other than today's date is needed use something other than
"Date()" in the Nz() functions. If something other than Null is stored for
absent AuditClose values, use an IIF() function and whatever criteria test
is dictated by whatever is stored (like for a zero-length string "", etc.).
Note: I'm pretty sure I counted the arguments and () correctly, my
apologies if they're off.


Sincerely,

Chris O.
 
Back
Top