6 Replies Latest reply: Dec 23, 2012 5:29 AM by Gysbert Wassenaar RSS


      Hi ,


      I have 3 columns Order number, Status, Date( which contains the date and time of the order) . Now in a staright table or pivot table i will use these columns but the background colour of the order number must be red if the difference between(in hours) Date and present date exceeds 48 hours whose status is 'Not delivered'.


      Example  :My data looks like this


      Order number, Status, Date

      12,       delivered,        2012-12-1 12:30:48

      10,       not delivered,   2012-12-22 10:02:21

      15,       not delivered,   2012-12-19 23:21:10


      . In this case for the orders 10 and 12 the background colour must be white(for the column order) since they are not exceeded 48 hours. Where as for order 15 it should display with red colour.



      In this there are 2 senarios . What if the time format is 24 hours and what if the format is 12 hours(am and pm).

        • Re: Date
          Gysbert Wassenaar

          This expression should do it if your Date field is already a timestamp. The formatting of the time doesn't matter.

          if(Status='not delivered' and Now()-Date>2, red(),white())


          But if your Date field is a string you need to make a timestamp of it first:

          for a 24 hour format: timestamp#(Date,'YYYY-MM-DD hh:mm:ss')

          for an am/pm format: timestamp#(Date,'YYYY-MM-DD hh:mm:ss tt')


          I've used the now() function which includes the time. If you want the start of today instead use the today() function.

          Now()-Date>2 means the difference between now() and Date is larger than 2 days, i.e. 48 hours.

            • Re: Date

              Hi Gysbert,


              I tried in different ways . But it is not giving what i require . More over Now()-Date nor TOday()-Date is not giving the correct output. my data looks like this

              Ticket No.Date Opened
              i-216654101-11-12 07:00
              i-216670701-11-12 09:11
              r-216709801-11-12 13:05
              i-216717701-11-12 13:53
              i-216737701-11-12 17:23
              i-216775802-11-12 06:39
              i-216853902-11-12 17:24
              i-216906405-11-12 02:44
              i-216908105-11-12 03:15


              Can you please create  a small application with this data and can you give me what i require

              and correct me where i am going wrong.


              In the application please use both NOW and Today functions for clear understanding how they work

            • Re: Date

              Hi Gysbert,


              Thankq but

              when i am trying to use this expression "if(Status='not delivered' and Today()-Date>2, red(),white())", it showing red colour to all those status which are nor delivered please assist. it is not taking today()- Date>2. Please assist


              • Re: Date

                Hey ,

                now it is working . But there is a second condition where it has to satisfy


                It has to show red if


                status is not delivered and

                State is not equals to 'closed or resolved'



                It has to satisfy all three conditions then only it should display in red colour please suggest

                  • Re: Date
                    Gysbert Wassenaar


                    if(Status<>'delivered' and State<>'closed or resolved' and Today()-Date>2, red(), white())


                    Or if closed and resolved are separate states try:

                    if(Status<>'delivered' and State<>'closed' and State<>'resolved' and Today()-Date>2, red(), white())