Pivot Table with Conditional Format Formula

[Neyme]

Hi,

Can one of forum experts please help me with creating correct formula?

In the pivot table (Sample DATA file > Pivot tab - attached) I need to highlight the values with today's date. The report will always contain today's date. For example, today's date is 032715 and report contains this date. The text string is always appended with the 6 digits date.

I know that the formula that I came up with is wrong and I need some help. I attached the screenshot ("Pivot") demonstrating how I used the Conditional Formatting with entered formula. Please note that the number of rows (Campaign IDs) for 5 Day and 28 Day email can vary.

=FIND(--MAX(RIGHT($B$5),6)))

Thank you so much!!!!

Sample DATA.xlsbPivot table.png

[FDibbins]

Try this as the CF rule formula...
=DATE(2000+RIGHT(RIGHT(B5,6),2),LEFT(RIGHT(B5,6),2),MID(RIGHT(B5,6),3,2))=TODAY()

[Neyme]

The formula works as a charm! Can you please explain what '2000' means in the formula? Is it a set number of rows?

Ideally, I need to return strings with today's date from column 'G' and corresponding to them values from column 'A' in a different tab. This will make the data 'clean' identifying only the campaign IDs for today. There will be many users using this Email Template and if possible, I would like to keep it clean, including only today's campaign IDs, so it will look as below. There are usually

• Four Campaign IDs for 5 Day email with TODAY’s date stamp
• Four Campaign IDs for 28 Day email with TODAY’s date stamp

Sheet 2
Column A Column B
5 Day 5day_latencyXXXXXX
5 Day 5day_latency_RZXXXXXX
5 Day 5day_retlatencyXXXXXX
5 Day 5day_retlatency_RZXXXXXX

28 Day latencyXXXXXX
28 Day latency_RZXXXXXX
28 Day retlatencyXXXXXX
28 Day retlatency_RZXXXXXX


Thank you SO much! You are a savior!


P.S. For some reason I cannot add reputation to your post. I get a message which I attached as a screenshot. How can I add more reputation for you?Here is the screenshot.

Cannot Add Reputation.png

[FDibbins]

Your date contains a 2-digit year, excel needs a 4-digit year for that, so...extract the 15 and add 2000

Thanks for the attempted rep (I cant see pics on here)