4 Replies Latest reply: Sep 9, 2013 10:51 AM by Nayan Lalla RSS

    Creation of a formula invoving blanks

    Nayan Lalla

      Hi

       

      I have created a (straight table on the front end) which is shown below.

       

      I am struggling  to get the comment part right.

       

      My formula should read as follows

      If the "Delvery Date" is greater than the "Expected Delivery Date",than the result should be "Late Delivery" , if not it should be "On Time".

      If the "Delivery Date" is blank, than the result should be blank, however i get the comment "On Time".  This is incorrect (ie On Time should not be the result if delivery date is blank)

       

      Here is my formula:

       

      if([Delivery Date]=' ',' ',if([Delivery Date]>[Expected Delivery Date],'Late Delivery','On Time'))

       

      Thank you

       

      kind regards

      Nayan

       

       

       

       

       

       

       

      Invoice NumberOrder DateInvoice DateExpected Delivery DateDelivery DateComment
      IN00000006739944-Mar-137-Mar-1315-Mar-13-On Time
      IN00000006740004-Mar-137-Mar-1315-Mar-13-On Time
      IN00000006750174-Mar-1312-Mar-1315-Mar-13-On Time
      IN00000006750184-Mar-1312-Mar-1315-Mar-13-On Time
      IN00000006750204-Mar-1312-Mar-1315-Mar-13-On Time
      IN000000067554612-Mar-1314-Mar-1312-Mar-13-On Time
      IN000000067658218-Mar-1319-Mar-1329-Mar-13-On Time
      IN000000067676212-Mar-1320-Mar-1322-Mar-13-On Time
      IN000000067676312-Mar-1320-Mar-1322-Mar-13-On Time
      IN000000067707612-Mar-1322-Mar-1312-Mar-13-On Time
      IN000000067776018-Mar-1327-Mar-1329-Mar-13-On Time
      IN000000069408319-Jun-1320-Jun-1325-Jun-1327-Jun-13Late Delivery
      IN000000069553026-Jun-1327-Jun-1326-Jun-1328-Jun-13Late Delivery
      IN000000069555226-Jun-1327-Jun-1326-Jun-1327-Jun-13Late Delivery
      IN000000069566920-Jun-1327-Jun-1325-Jun-132-Jul-13Late Delivery
      IN000000069572720-Jun-1328-Jun-1325-Jun-132-Jul-13Late Delivery
      IN00000006960441-Jul-131-Jul-135-Jul-132-Jul-13On Time
      IN00000006961031-Jul-131-Jul-135-Jul-132-Jul-13On Time
      IN00000006961071-Jul-131-Jul-135-Jul-132-Jul-13On Time
      IN00000006978108-Jul-1310-Jul-1315-Jul-1317-Jun-13On Time
      IN000000069937617-Jul-1317-Jul-1330-Jul-1324-Jun-13On Time
      IN00000007031201-Aug-132-Aug-135-Aug-137-Jun-13On Time
      IN00000007038235-Aug-136-Aug-138-Aug-138-Jun-13On Time