2 Replies Latest reply on Jul 16, 2010 8:25 AM by FM9NewUser

    Time Calculations

    FM9NewUser

      Title

      Time Calculations

      Post

      I have several fields that calculate elasped times.  They work great using Timestamp; however, there are instances where I don't have all of the dates and/or times.  This produces a "?" or erroneous number to appear in my calculation field.  I have tried using CASE to blank out the field but have been unable to get it to work.  Can anyone help?  Below is an example:

      My fields are Referral Date; Referral Time, Recovery Date; Recovery Time

      My original formula (thanks to this forum) was:

      If(Timestamp (Recovery Date;Recovery Time) - Timestamp (Referral Date; Referral Time)<0; " ";  (Recovery Date;Recovery Time) - Timestamp (Referral Date; Referral Time))

      This worked GREAT for those rare instances where the recovery was before the "official" referral - instead of giving me a negative number, it blanks the field.  But, how can I get it to blank out the field if any of the four fields used in calculation are unknown?

      I tried CASE (

      Referral Date=" ";" ";

      Referral Time=" ";" ";

      Recovery Date=" ";" ";

      Recovery Time=" "; " ";

      (Timestamp (Recovery Date;Recovery Time) - Timestamp (Referral Date; Referral Time)<0; " ";  

      (Recovery Date;Recovery Time) - Timestamp (Referral Date; Referral Time))

      Thank you for any help!