Calculating Date and Time using IIf statements - Please Help Me

[ASSK]

Hi,

This is what I am trying to achieve, I have a form with the following:

Received Date:
Received Time:
Sent Date:
Sent Time:
Total Elapsed Date: With This formula =DateDiff("d",[Received Date],[Sent
Date]).
Target Acheived:


In the target Achieved Control Box I am using this iif statement to
calculate if target is acheived or not with a "Yes", "No":

=IIf([Time Rec]>="12:00 PM" And [Time Sent]<="12:00 PM" And
DateDiff("d",[Received],[Sent]>=2),"Yes",IIf([Time Rec]<="12:00 PM" And [Time
Sent]<="5:00 PM" And DateDiff("d",[Received],[Sent]<=1),"Yes","No"))

For some reason, it always come back as "Yes"....

Basically the rules are:

If request is received before Midday 12:00pm, then response must be sent by
17:00 the next business day and if request is received after Midday 12:00 pm,
response must be sent by No Later than Midday 12:00 Pm the second Business
day.

I am trying to measure the KPI.

I hope this make sense...

 

[Allen Browne]

Presumably your first 4 fields are all Date/Time type (not Text type)?

If so, your expression needs to use # delimiters around the values, i.e.
#12:00:00#
not quotes (like "12:00:00".)

The general idea is to add 41 hours (1 day + 17 hours) if the time is before
midday, or 96 hours (2½ days) if it's after midday? So the due date/time
value you want is:
DateAdd("h",IIf([Received Time] < #12:00:00#, 41, 96), [Received Date])
and you would then compare [Sent Date] + [Sent Time] to that value.

You may find it easier to combine the date and time into one field, i.e. a
combined ReceivedDateTime, and a SentDateTime.