9 Replies Latest reply on Jan 28, 2013 10:08 AM by TomForbes

    Date Calculation Using Case Function and Greater Than Not Working

    TomForbes

      Title

      Date Calculation Using Case Function and Greater Than Not Working

      Post

           I need to calulate the mileage costs based on various rates within ranges of dates.

           For example, if the date of travel falls between July 1, 2012 and December 31, 2012 the travel cost value = .555, and if the travel date falls between January 1, 2013 and June 30, 2013 the travel cost value = .565

           I have tried the following case function for the field <<mileage_cost>> with 2 records that contain the dates "12/12/2012" and 1/31/2013" respectively. The function returns the value ".555" for both cases, but should return ".565" for the second case.

           Case(
           travel_date > 6/30/2012; .555;
           travel_date > 12/31/2012; .565
           )