I am attempting, quite unsuccessfully, conditionally format a range of cells using the Excel (2013) LEFT function to extract data from a date in one column and use the result to underlaine the range of cells.

The date has been entered in column B (starting at row 18) in Australian format - e.g. 30/6/15 - and formatted the display as 30-Jun-15.

I am highlighting the range of cells from b18 through S12875 then selecting Conditional Formatting/Rule.

I am writing the rule as =if(left($b18,4,"30/6"),1,0) and then formatting to underline the part(s) of the range for which that condition is true.

It does not work! I've even tried =if(left($b18,6,"30-Jun"),1,0) in case the display format made a difference!

Any ideas on how to make this work?

Thanks.

If your reference cells contain actual dates and you're concerned to match those containing 30/6/2015, you will need to test the numeric value of that date (42185), not its formatted representation.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thank you for the speedy reply. I'll try that and see if it works. I'll get back to you one way or the other!

Well, that SORT of worked but still does NOT do as I want.

Firstly, I have discovered that to use your suggestion, I needed to make the formula read: =if(INT(LEFT$b18,6))=42185,1,0) Note the addition of the INT

However, my date cells in column B are calculated.

Cell B18 has the starting date entered as 4/1/71 and formatted to read 4 January 1971.

Cells B2 down to 30 June 2021 are calculated from $b18 (relative) with the formula =$b18+1 and I do NOT want to change these to absolute values.

So, the conditional formatting formula above does NOT return the value 42185 for every 30 June.

Basically I want every row that is related to a 30 Jun date, no matter what year, to be underlined in red so I don't have to manually find all those rows and manually select the cells in that row to format and then format underline them! That is a REAL pain in the backside!

So, is this task at all possible?

Thanks for the feedback so far.

Why are you persisting with the LEFT function? Surely you could use:
IF(B16=42185,1,0)
You'd only need to use:
IF(INT(B16)=42185,1,0)
if the value in B16 might include a time component.
In either case, the fact the date in B16 might be calculated makes no difference.

As for
I did say 42185 was the value for 30/6/2015. If you want to disregard the year component, you could use a formula like:
=IF(AND(Month(B16)=6,DAY(B16)=30),1,0)
Again, the fact the date in B16 might be calculated makes no difference. In this case, neither does the possibility that the date might include a time component.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]