"Yes" if Date difference (hours/duration) is met

[rfigueroa1976]

I need a column to calculate time between two dates and tell me if the time is less than 24 hours.

Order Ship Date: ord_origin_latestdate <the format is MM/DD/YY HH:MM:SS>
Order Book Date: ord_bookdate <the format is MM/DD/YY HH:MM:SS>

I'm calculating the variance between the two as follows: Book to Pickup Variance: ([dbo_orderheader].[ord_origin_latestdate])-([dbo_orderheader].[ord_bookdate]) <the format this comes back as is 00.00000 (not HH:MM:SS)>

The column i want next to the variance should answer the question "is the variance less than 24 hours (Yes, No)

Something like: Rush order: IIF(([dbo_orderheader].[ord_origin_latestdate])-([dbo_orderheader].[ord_bookdate])<24:00:00, "Yes", "No") <-- obviously this doesn't work.

Thanks for your help in advance!

[ssanfu]

I've seen this a lot where people (including me a long time ago) confuse time and duration.

Technically, 24:00:00 doesn't exist. 23:59:59 is 1 second before midnight. Add 1 more second, and you get 00:00:00 (midnight).

Subtracting 2 date/times and you get a duration. The difference between 8:00:00 am and 10:30:00am is 2.5 hours or 2 hours and 30 minutes, but NOT 2:30!
You can never write the difference as 2:30:00.

Access is pretty good at doing date/time math.
Try:

IIF(([dbo_orderheader].[ord_origin_latestdate])-([dbo_orderheader].[ord_bookdate])<1, "Yes", "No")

(edited)
In Access I would use the function DateDiff()
the following are equivalent:

IIF(((DateDiff("n",[dbo_orderheader].[ord_origin_latestdate]),([dbo_orderheader].[ord_bookdate]))/60)<24, "Yes", "No")

IIF((DateDiff("n",[dbo_orderheader].[ord_origin_latestdate]),([dbo_orderheader].[ord_bookdate]))<1440,  "Yes", "No")

(there are 1440 minutes in 24 hours...)

[pbaldy]

Wouldn't this need to be <1?

IIF(([dbo_orderheader].[ord_origin_latestdate])-([dbo_orderheader].[ord_bookdate])<24, "Yes", "No")

the integer portion of the result would be days, not hours. Or am I just hungry since I haven't had dinner yet?

[rfigueroa1976]

THIS F#*(()*&()*#&(* WORKED! You da Man!

Thanks!

I've seen this a lot where people (including me a long time ago) confuse time and duration.

Technically, 24:00:00 doesn't exist. 23:59:59 is 1 second before midnight. Add 1 more second, and you get 00:00:00 (midnight).

Subtracting 2 date/times and you get a duration. The difference between 8:00:00 am and 10:30:00am is 2.5 hours or 2 hours and 30 minutes, but NOT 2:30!
You can never write the difference as 2:30:00.

Access is pretty good at doing date/time math.
Try:

IIF(([dbo_orderheader].[ord_origin_latestdate])-([dbo_orderheader].[ord_bookdate])<24, "Yes", "No")

In Access I would use the function DateDiff()
the following are equivalent:

IIF(((DateDiff("n",[dbo_orderheader].[ord_origin_latestdate]),([dbo_orderheader].[ord_bookdate]))/60)<24, "Yes", "No")

IIF((DateDiff("n",[dbo_orderheader].[ord_origin_latestdate]),([dbo_orderheader].[ord_bookdate]))<1440,  "Yes", "No")

(there are 1440 minutes in 24 hours...)

[ssanfu]

Wouldn't this need to be <1?

Yes, you are right! Great catch - corrected it.... (Error was PBKAC.)

THIS F#*(()*&()*#&(* WORKED! You da Man!

[rfigueroa1976]

So, I'm reviving this thread in hopes to get some clarification. So, the original formula did work for what I originally asked for but, does not completely solve the question.

The formula that helped was as follows: IIf(DateDiff("n",[dbo_stops].[stp_arrivaldate],[dbo_orderheader].[ord_dest_latestdate])<1440,"Yes","No")

This formula answers the question "is the difference between the two dates less than 1440 minutes (or 24 hours)"... The "Yes" will be triggered regardless of the difference being plus or minus 24 hours.

The trigger that I now need is : if Arrival date - latest delivery date is less then 15 minutes late, trigger "No", else Trigger "yes". (The question that i'm trying to answer is, was the delivery made greater than 15 minutes from the requested delivery date/time). I'm assuming it would be something like IIf(([dbo_stops].[stp_arrivaldate]-[dbo_orderheader].[ord_dest_latestdate])>0015,"Yes","No") However, this is not working.

Please help.

[Minty]

You've missed out the DateDiff() part ;

IIf(DateDiff("n",[dbo_stops].[stp_arrivaldate],[dbo_orderheader].[ord_dest_latestdate])>15,"Yes","No")

[rfigueroa1976]

Thanks for the reply. That is telling me if the difference is 15 minutes or greater, regardless of plus or minus difference. I need a "Yes" only if minutes is > 15 minutes. I hope i'm making sense.

So, was the delivery made 15 minutes late or greater, "Yes", "No"?

[Minty]

No if it can be either way around you need to use the ABS() function to return a positive integer, wrapping it up like so;

IIf(Abs(DateDiff("n",[dbo_stops].[stp_arrivaldate],[dbo_orderheader].[ord_dest_latestdate]))>15,"Yes","No")

[rfigueroa1976]

I must be doing something wrong.... I'm getting "Yes" regardless of the time being neg or poss....

Delivered Passed 15 MIN Window: IIf(DateDiff("n",[dbo_stops].[stp_arrivaldate],[dbo_orderheader].[ord_dest_latestdate])<15,"No","Yes")

[ssanfu]

Can you post data (times) for [dbo_stops].[stp_arrivaldate] and [dbo_orderheader].[ord_dest_latestdate] to be able to match your results picture??


Edit:
How are you getting numbers less than 1???? I get whole numbers.....

[rfigueroa1976]

So, I have two dates,
1) Customers requested delivery date/Time (12/10/2017 14:30). This comes from [dbo_orderheader].[ord_dest_latestdate]
2) Actual Delivery date/time (12/10/2017 15:15). This comes from [dbo_stops].[stp_arrivaldate]

The fact that the delivery occurred 45 minutes after the requested delivery time, the time duration should be in the positive and the flag that i want should say "Yes".
The way that the negative can happen is if the delivery occurred before the customer requested time (requested delivery time of 14:30 and actual delivery occurred 14:10 - 20 minutes early or -0:00:20 (excel will convert this to -0.013888889)

[isladogs]

You calculated values are based on days "d" rather than minutes "n"

In your DateDiff line do you have "d" instead of "n"?

[June7]

The calculation works for me with my test data. Provide sample of your data.

[rfigueroa1976]

The exact formula i have is: Delivered Passed 15 MIN Window: IIf(DateDiff("n",[dbo_stops].[stp_arrivaldate],[dbo_orderheader].[ord_dest_latestdate])<15,"No","Yes")