0 Replies Latest reply: Mar 5, 2013 6:27 AM by Dan-Ketil Jakobsen RSS

    Datefunction in interval

    Dan-Ketil Jakobsen

      Hi

       

      I have a formula where I compare two dates and if the interval is > 0 I want to dispaly the intveral

      This consists of a createdate and a shippingdate.

      The shipping date is a construction of two parameters.

      I need to check if the real depaturetime is less then the esteimated one. If so it is actually sent the next day

       

      This function seems to give the correct date.

      =Date#(

              IF([Real shipdate] < [Est ship date ],

                  DATE(DATE(WantedShippingDate)+1,'DD-MM-YYYY') & ' ' & TIME([Real shipdate ]),

                  DATE(DATE(WantedShippingDate),'DD-MM-YYYY') & ' ' & TIME([Real shipdate]))

      ,'DD-MM-YYYY HH:MM:SS')

       

       

       

      I orgiialy had a formula where I compared the two dates:

       

      =IF(INTERVAL(max(MaxCreateDt1)-max(WantedShippingDate), 'mm:ss') > 0,INTERVAL(max(MaxCreateDt1)-max(WantedShippingDate), 'mm:ss') ,'-')

       

      Now I have to change the  WantedShippingDate with the date expression above. But it results in blank...or rather the sign - .

       

      =INTERVAL(max(MaxCreateDt1)-max(

                                                               Date#(

                                                                         IF([Real shipdate] < [Est ship date ],

                                                                              DATE(DATE(WantedShippingDate)+1,'DD-MM-YYYY') & ' ' & TIME([Real shipdate ]),

                                                                              DATE(DATE(WantedShippingDate),'DD-MM-YYYY') & ' ' & TIME([Real shipdate])),'DD-MM-YYYY HH:MM:SS')

                                                                           )//END IF

        , 'mm:ss')

       

      Any comments?

       

       

      BR

      Dan