excel conditional formatting

[keithinoz]

In the attached simple Excel file, in column b is the monthly data and column c the YTD data. I want to "hide" the YTD duplicate values so that table looks like the red version. I tried conditional formatting using the duplicate values and the formatting by changing the font colour to white but that whites out all the numbers. Help please

 

[SkipVought]

Hi, Keith.

YTD? Fiscal starts on July 1?, regardless the day of week? Or is YTD based on actual year, regardless of week's start?

Also its odd to just use text months in a spreadsheet rather than actual dates FORMATTED to display months.

So wouldn't you want to "hide" future months YTD? Of course you couldn't accomplish that without actual dates in column A.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[keithinoz]

In Australia, the financial year starts 1 July. RE showing the months, it suits to me to see them all

 

[SkipVought]

[pre]
Amount YTD in Column A
1-Jul 23 23 A2: Actual YTD Start Date
1-Aug 23 A3: [tt]=DATE(YEAR(A2),MONTH(A2)+1,1)[/tt] copied down
1-Sep 23
1-Oct 23
1-Nov 23
1-Dec 23
1-Jan 23
1-Feb 23
1-Mar 23
1-Apr 23
1-May 23
1-Jun 23
[/pre]

SELECT C2:C13
Open CF Wizard
Select FORMULA
formula: =YEAR(A2)&TEXT(MONTH(A2),"00")>YEAR(TODAY())&TEXT(MONTH(TODAY()),"00")

[URL unfurl="true"]https://res.cloudinary.com/engineering-com/raw/upload/v1626841902/tips/example_2_zz2k6l.xlsx[/url]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[keithinoz]

Appreciate that but it doesn't solve my original problem of not wanting to see all the "23" othe rthan the one for July

 

[SkipVought]

Did you download the workbook? That's what you'll see.

Edit: So sorry my CF formula was incorrect above, but correct in the workbook I uploaded.

I corrected the formula above.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[keithinoz]

Thanks for your help