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

    Date

      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'

                Today()-Date>2

                 

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

                  • Re: Date
                    Gysbert Wassenaar

                    Try:

                    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())