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.