Date math in query - strange results

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

Guest

My query returns
OpenDate, CloseDate, DaysOpe

wit
iif(NZ(T.CloseDate,"")="",Now() - T.OpenDate,T.CloseDate - T.OpenDate) AS DaysOpe

It seems to work when there is no CloseDate but when it subtracts OpenDate from Close Date, I get a negative number with an exponent. I need the result in days, i.e. .5 for half a day. How do I do this? Any help appreciated.
 
Try

IIF(T.CloseDate Is Null,
DateDiff("h",T.Opendate,Now())/24,
DateDiff("h",T.OpenDate,T.CloseDate)/24)

DateDiff("h",Date1,Date2) will return the number of hour boundaries crossed
between any two date/times. So, it may not be quite as accurate as you want
since the number of hour boundaries between 10:59 AM and 11:01 AM is one even
though only two minutes have elapsed.
 
Back
Top