Conditional Formatting formula does not work even though formula is returing correctly

[random11]

Column A contains a row heading... then every other cell contains either a date time... or a length of time.

Ex:

Please Login or Register  to view this content.

The date time is formatted so that it is not recognized by excel and so I created a new column B to parse the date times. Using a formula I look if the cell in column A has the letter 'Z', this way I know it should be a date time, then I parse it accordingly (adjusting for timezone). For the other rows (the time lengths), the values are just copied to column B as is.

I want to have a conditional format in order to format the date time rows separately from the time length rows as follows:

Please Login or Register  to view this content.

The date times should include the 'weekday', 'month' and 'day of month'... only if it is the first date time of that weekday in the column, otherwise it should be formatted to just the time in a 12 hour AM/PM format. The time lengths rows should not be changed.

First I wrote a conditional formatting rule to format all the date times as a 12 hour time. The formula subtracts 1 from the date time and gets its weekday() then checks if that is an error (the time lengths will evaluate as an error when the weekday function is called because the date is negative)

Formula:

Please Login or Register  to view this content.

Format:

Please Login or Register  to view this content.

Then I wrote another condition for the first weekday of the column to have the day of week and the month included but I couldn't get it to work correctly.

I wrote my formula down in column C to see what it was returning and it is correctly returning TRUE and FALSE for the specific rows.

Formula:

Please Login or Register  to view this content.

Format:

Please Login or Register  to view this content.

It first does a check on the row number... if it is less than 2 it should return true because its the start of the column and therefor will automatically be the first of that weekday. Then it checks if the current row is a proper date time (vs a time length). Then it looks two rows up (at the previous date time) and checks to see if the weekday of that date is equal to the weekday of the current cell minus 1 (ie the day before), if it is that means the current cell is the first of this weekday in the column and should include the extra formatting.

For whatever reason, when I use this formula as the conditional rule only the first date time includes the month and such. If I change the rule so that the formula just points to column C (the result of the formula in the sheet), everything works as intended.

Can anyone tell me why the formula is behaving differently when used as a conditional formatting rule? And what I need to do to fix this?

Thanks

[random11]

After more research seems to be an issue with INDIRECT ROW and COLUMN. I updated my formula to this:

Please Login or Register  to view this content.

And I got it to work.