4 Replies Latest reply on Feb 27, 2015 7:16 AM by Hamel Patel

# how to subtract two dates then apply formatting with shape and colour

I want to be able to take for example november 12 minus november 11 and then what ever the number is for e.g apply a shape and a colour to that shape for e.g

if its x% then a orange circle

if its y% then a grey diamond

if its z% then a blue square

• ###### 1. Re: how to subtract two dates then apply formatting with shape and colour

Hi,

use No fo days= Datediif('day', Date1, Date2)

apply if <=30 then x

>30 then y

Hope this will work..

Regards

Jaideep

• ###### 2. Re: how to subtract two dates then apply formatting with shape and colour

Hi Jaideep,

let me apply a bit more context to this.

I need to make it so whatever date they select this works this includes month e.g December 1st and 2nd they get the difference.

My data is like this

January            February

1         10                   25

2         14                   17

3         24                   11

4        3                       7

So if they select january 1st and 2nd it would be 14-10=4 which is a 40% increase on the previous day hence a orange circle.

Is that possible so it is based on the two dates that are selected in the filter.

Thank you,

Hamel

• ###### 3. Re: how to subtract two dates then apply formatting with shape and colour

Hi,

We can use the Table calculations to get the Difference from Previous then we can use those calculations to apply the formatting like the following way,

1) Drag the Year, Month Columns onto Roe shelf,

2) Create a calculated field,

Name: Difference Percentage

(ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1))/ LOOKUP(ZN(SUM([Sales])), -1)*100

3) Create another calculated field using the above calculated field.

4) Select "Shapes" as mark then Drag the [Conditional Formatting - shapes] field onto shapes.

Then edit then ASSIGN shapes 1 By 1.

5) Drag the [Conditional Formatting - shapes] drop into "Colors Marks Pill".

6) Edit the Colours then apply 1 by 1.

• ###### 4. Re: how to subtract two dates then apply formatting with shape and colour

Hey,

your answer was really helpful and has got me 80% of the way there however, on your last else "green diamond" i need a if null function to say if it is null then leave it blank but i cant seem to get it to work.

What happens is because its null it puts it as a green diamond.

Thanks,

Hamel