Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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).
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())
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.
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-2166541 | 01-11-12 07:00 |
i-2166707 | 01-11-12 09:11 |
r-2167098 | 01-11-12 13:05 |
i-2167177 | 01-11-12 13:53 |
i-2167377 | 01-11-12 17:23 |
i-2167758 | 02-11-12 06:39 |
i-2168539 | 02-11-12 17:24 |
i-2169064 | 05-11-12 02:44 |
i-2169081 | 05-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
See attached example
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
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
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())