Conditional Formatting formula not working

Greeting,

I am having strange issue with my 'Conditional Formatting' formula. It was working fine from couple of month back, but some-home the formula in 'Conditional Formatting' not working at all.

I have month drop down list in X7 and manually typed date in C5. What I was trying to achieve is to match the month selected in X7 with month C5 and vice-versa.
But if month in X7 changes month in C5 get strikethrough and if both month are same in X7 & C5 then should not be strike-through. This which is some how not working now.

Please Login or Register  to view this content.

Please see the attach Excel file.
Condintional Formating.xlsx

Any solution on this will be appreciated.

Thank you

Best,

Hi pipsmultan,

FWIW your spreadsheet is working fine for me. You need to troubleshoot why the formula in your conditional format isn't returning what you'd expect. I'd suggest copying it into a cell on your spreadsheet and evaluating the values within it using the F9 key. E.g. what result is being returned by the TEXT($C5,"mmmm") part of your formula and what is being returned by $X$7? There'll be a reason why they don't match.

Hope this helps.

Snook

Originally Posted by The_Snook

Hi pipsmultan,

FWIW your spreadsheet is working fine for me. You need to troubleshoot why the formula in your conditional format isn't returning what you'd expect. I'd suggest copying it into a cell on your spreadsheet and evaluating the values within it using the F9 key. E.g. what result is being returned by the TEXT($C5,"mmmm") part of your formula and what is being returned by $X$7? There'll be a reason why they don't match.

Hope this helps.

Snook

Hi Snook,

Thank you for your reply. Yeah its very strange CF formula not working for me. I have done the test using F9 key please see the attach image

001dd.jpg

Is there any other formula or way out i can use instead of the below formula, as this formula use to work for me without any issue on Excel 2010 but as i have upgraded to Excel 365 its not working anymore.

Please Login or Register  to view this content.

Best,
Naveed Arif

Originally Posted by The_Snook

=MONTH($C5)<>MONTH(DATEVALUE($X$7&" 1"))

Very strange this formula only work for the month 'Aug, Sept, Nov, Dec. If i chose another many its doesn't work.

Originally Posted by The_Snook

PS - The conditional format in your screenshot looks ok because neither of the dates input are in February.

It does't mater which month you I chose C5 will remains struck-through

Best,
Naveed Arif

The CF worked for me okay, although the cells C7 and C8 were also highlighted even though they were empty. You can change the CF rule to this:

=AND($C5<>"",TEXT($C5,"mmmm")<>$X$7)

to avoid that from happening.

It also occurred to me that you might need to use a different letter than "m" in your format string, if the Swedish word for Month does not begin with M. In French you have to use "aaaa" instead of "yyyy" for year (annee), so it might be something similar with you.

Hope this helps.

Pete

It's difficult to diagnose because I'm not experiencing the same issue as you.

When you select January as the month what numbers are being returned in the formula bar when you select and 'F9' the 'MONTH($C5)' and 'MONTH(DATEVALUE($X$7&" 1"))' portions of the formula? The formula bar should then look like this '=1<>1'.

Originally Posted by Pete_UK

You can change the CF rule to this:

=AND($C5<>"",TEXT($C5,"mmmm")<>$X$7)

Hi Pete thank you for help but when we apply your CF rule formula we get below error message. Can you please check and help. Thanks

error.jpg

Originally Posted by Pete_UK

It also occurred to me that you might need to use a different letter than "m" in your format string, if the Swedish word for Month does not begin with M. In French you have to use "aaaa" instead of "yyyy" for year (annee), so it might be something similar with you

I think you right it might be because of the format string. We normally share our files colleagues in Asia and back and forth.

Is there any general CF rule that we can use so we doesn't this kind of error as give us a a lot confusion.

Best,

Originally Posted by The_Snook

It's difficult to diagnose because I'm not experiencing the same issue as you.

When you select January as the month what numbers are being returned in the formula bar when you select and 'F9' the 'MONTH($C5)' and 'MONTH(DATEVALUE($X$7&" 1"))' portions of the formula? The formula bar should then look like this '=1<>1'.

Hi Snook,

Yes this can be difficult to diagnose. But we think this is because of different Zone format string. As we are located in Sweden and our colleagues in Asia, they normally did counter this issue only we i think.
Is there any general CF rule that we can use for these kind of these errors.

Thanks!

Best,

Hi pipsmultan,

Not that I'm aware of, my knowledge doesn't extend to the nationality differences between products I'm afraid.

Regards,

Snook

HI Snook,

No worries, Thank you for you help. We are figuring out the solution but haven't reach to a solution yet.

Best,
Naveed Arif

HI Pete,

Please Login or Register  to view this content.

Yes i have followed the CF | Manage Rules....it not allowing us to add the formula. Its give same error as i have attach above.
Can you please add your formula in the our attach excel sheet. Let us check if that works for us when we open here. Thanks

Attachment 618699

Do your colleagues in Asia change the names of the month to suit their location?

No i don't think they change any thing, but there computer is in English and Yes my PC set up for Swedish.. I have tired different ways to enter the formula but no luck.

Best,

Check my edits - use semicolons instead of commas:

=AND($C5<>"";TEXT($C5;"mmmm")<>$X$7)

Hope this helps.

Pete

Originally Posted by Pete_UK

Check my edits - use semicolons instead of commas:

Hope this helps. Pete

Hi Peter,
Thank you its all fix now appreciated your time and effort. Thread marked as Solved and Reputation has added

Have great weekend

Best,

Thanks for that - glad you got it working in the end.

Pete