I am working in a pivot table within excel 2010. There are three columns I am working with, 2 of which are dates, the third is a column with a number. I am trying to find the difference between the first two dates and if the difference is larger than the third column with the number, I would like to format it a certain way within the conditional formatting options. The true issue is that every formula I know, gets messed up when the pivot table changes. I need something that stays consistent within the pivot table's data changes.

✔ Best Answer

Okay - so we need to see how the access table is formatted. The field for the translation must be Numerical. Can you change the Access table?

~~Also - is the Pivot done in Access or are you importing the tables into Excel?~~

never mind - I just saw your first line of your first post.But we should look at how you are getting the information from Access to Excel.

Hi Tori It's a little difficult to picture what the precise problem is. I would guess that what you are doing is creating the pivot and then adding additional column next to the pivot to calculate the difference and this is what gets messed up. Is this what's happening?

If it is, I think you can resolve your problem without the additional columns by including the calculation you want in the conditional format. For example, if you have your dates in columns C and D and your number in column E you can have the conditional format say

=$D2-$C2>$E2

This would eliminate the need for the additional column next to the Pivot table.

If this is way off the mark - could you paste an example of your data with further explanations? (when you paste your data please use the "pre" button)

Let us know...

Thanks for your response. I was trying to add an additional column because every conditional formatting option I have tried does not work within the pivot table. However if I can work within the table, this would be preferable. I have even considered going back to the Access database and trying to bundle the dates together, but I'm relatively new at Pivot Tables and Access. My columns are as such:

Column L = P1, P2 or P3 (Stands for Priority type)

Column M = Used a formula to turn column L into numerical value such as P1 = 3, P2 = 7, P3 = 14 (number represents days)

Column N = Date 1 - Date Project is presented

Column O = Date 2 - Date of CompletionI want to conditionally format either one cell or the entire row if Date 2 (O) - Date 1 (N), is greater than Column M's different numerical values corresponding with their priority.

Ultimately I want to show when we have surpassed our deadline to complete a project based on it's priority. For example, If I have a project that is P1 (L), which represents in 3 days due (M), the difference between my completion date (O) and my start date (N) should not be greater than 3 days (M).

I have tried to conditionally format with the formula as described above, but it has not worked. Do I need to select the data in a particular way?

PRICING_PRIORITY P1-4 IN DAYS PRICE_EFFECTIVE_DT PRICING_COMPLETED_DT P3 14 7/13/2012 7/23/2012 16:53 P1 3 11/29/2012 11/30/2012 13:32 P3 14 8/15/2012 8/17/2012 15:43 P2 7 7/27/2012 8/8/2012 13:06 P3 14 10/9/2012 10/23/2012 13:38 P3 14 9/24/2012 9/27/2012 14:18

Hi Tori It worked for me with this conditional format

=$O2-$N2>$M2

Applied to this range

=$L$2:$O$7

Notice that the first row of the range must equal the first row of the data being analysed - in this case row 2. Also notice the relative referencing - with the "$" in front of the Column and nothing in front of the Row.

I'll try to post pictures of what I've done ...

Here is a picture of the data that should match your arrangement (date formats are altered for my neck of the world). It also shows the result of the conditional format -----> 2012-12-14_0850

Here is the Conditional Rule and the format I specified -------> 2012-12-14_0852

Finally, this is the range it is applied to - this can be adjusted to whatever range you like. ------> 2012-12-14_0853

This is the data as it appears in the spreadsheet. Let me know if it is not the same as you have

PRICING_PRIORITY P1-4 IN DAYS PRICE_EFFECTIVE_DT PRICING_COMPLETED_DT P3 14 13/07/2012 23/07/2012 16:53 P1 3 01/11/2012 30/11/2012 13:32 P3 14 15/08/2012 17/08/2012 15:43 P2 7 27/07/2012 08/08/2012 13:06 P3 14 09/10/2012 23/10/2012 13:38 P3 14 24/09/2012 27/09/2012 14:18

Unfortunately this is still not working for me. I tried this exactly as you stated, however my row starts on the 5th row. Used formula in conditional format

=$O5-$N5>$M5

Then under Manage Rules, I changed the range to below to get a bigger sample data.

=$L$5:$O$25

It is still not working. I did have a previous issue with a former excel document and I needed to restart in a new excel in order to get my formatting to work. Perhaps this is a glitch/virus.

Hi Tori It's a mystery. 2 things come to mind...

Make sure that your dates are in date format. In another column try the formula

=M5-O5-N5

to confirm that you are getting what you expect.

Also, could there be other conditional formats that may be interfering with this one? Just to be sure, when you click on Conditional Format, the 2nd to last choice is Clear rules. Click on that and then re-enter this rule. Also, try different formats this time e.g. fill with a noticeable colour and font that is not "Automatic"

Beyond those checks I'd start thinking about the "Repair" facility and possibly re-installing Office.

Good idea with the dates, I've updated to "short date" form. Yup, I've cleared all the rules, and added this one again. Still didn't work.

If I do =M5-O5-N5 it does not get what I need. I'm assuming because the numerical value in M is not a date like the other two values. However O5-N5 does work correctly. I could format this new column, it would just be frustrating to use with the pivot table when changing the parameters. I would either need to apply the formula to the whole row and get a bunch of "O's" or need to reapply formatting whenever the data changes, which I'm hoping to avoid.

Thanks for your help. I'll see what I can do with a repair. Thank you!

One other thing that I just tested out. If the data you are trying to format is a pivot table then there are some special rules that apply to them. To get past those rules start your conditional rule outside the pivot i.e. if your pivot starts in M4 start your rule as M1 and your range starting in row 1 as well.

WAIT!!!!! That;s the answer - your translation of prioritiy is not a Number!!!!! (I'm shouting really)

Show me your equation that translates Priority to a number of days - you prabably have a space in there.

Could be... I actually don't have an equation, because again the pivot table wouldn't keep the info as it changed data. However in Access I created a table that give my values P1 = 3, P2 = 7, P3 = 14 and P4 = 21.

The value is listed in the Pivot Tables Row Labels. However it is not formatted to a number.

Okay - so we need to see how the access table is formatted. The field for the translation must be Numerical. Can you change the Access table?

~~Also - is the Pivot done in Access or are you importing the tables into Excel?~~

never mind - I just saw your first line of your first post.But we should look at how you are getting the information from Access to Excel.

GENIUS! Although, now I am having one more issue using the same formula we started with. It is highlighting my rows, but it's not the correct ones. It is actually randomly highlights, some priorities are being met while other are not!

For example it is highlighting the first two rows, but the difference in days in the first row is 10 but we actually have 14 days to complete. Same with the second row.

I am using:

=($O8-$N8)>$M8 applied to =$L$5:$O$1000

P3 14 7/13/2012 7/23/2012 10.70414352 P1 3 11/29/2012 11/30/2012 1.564386574 P3 14 8/15/2012 8/17/2012 2.655092593 P2 7 7/27/2012 8/8/2012 12.54586806 P3 14 10/9/2012 10/23/2012 14.56821759Do I need to put $ in front of the first equation's numbers???

No - everything is off by 3 rows because your rule starts at O8 and your range starts at L5 Start your range at L

8Or change your rule to O5

Just did. Everything works! Thank you so much, this was driving me crazy all week.... I really appreciate it!

That's great because it was starting to drive me crazy too.

Ask Your Question

Weekly Poll

Would you buy mobile phone service from Google?

Discuss in The Lounge

Poll History