Formula to display records within 24 hr period

[mvalley]

I am VERY new to Crystal writing and still trying to understand formulas. I need to create a formula to display cases canceled within 24 hrs of booked date. Fields are:
booked_date = booked date
book_audit_datetime = canceled date
Any suggestions? Thanks for any help.

 

[briangriffin]

In the selection formula put:

not isnull(book_audit_datetime)
and
datediff("h",book_audit_datetime,booked_date) <= 24

This will give you the difference in hours between the two dates. However, if the difference is 24.5 hours it will return 24. If you changed the <= 24 to <= 23 it would return everything with a difference up to 23.99 hours. If there were a record with a difference of exactly 24 hours it would be missing.

So if you need more precision do the same thing with minutes, then divide by 60:

not isnull(book_audit_datetime)
and
datediff("mi",book_audit_datetime,booked_date)/60 <= 24

 

[mvalley]

Thanks, but when I write this formula I get the error message telling me the 24 needs to be a date/time

 

[briangriffin]

Sorry, should be:


not (isnull(book_audit_datetime))
and
datediff("mi",book_audit_datetime,booked_date)/60 <= 24

If this gives an error, can you confirm that both fields are a datetime datatype?

 

[lbass]

For minutes, you need to use "n".

-LB

 

[mvalley]

Thank you both. The formula works with the "n".