Column formatting dates broken - or am I going mad?

[Rob Clarke]

Hi,

I noticed that some of my column's formatting (background colour fills depending on date) was returning odd colours.  As I was using my own JSON code I decided to clear out the code and just use the default "Format Dates" option just to see what is going on.

What's wrong with this image?

All the colours are appear to be mixed up, just for info todays date is 7/11/22, so the top three rows should be red and the bottom row should be green.

The same happens if I use the conditional formatting, this should change everything to green

But it doesn't, if I change it to "is on or before" then they all light up....which is backwards!

I've tried different browsers to make sure it's not a cookie/cache issue but the result is the same.  The column in question is a calculated column, not sure if that's the reason but it was working in the past.

Anyone seen anything like this? any thoughts as I am getting brain melt now :)

Cheers

[#2]

@Rob Clarke Is it considering dates in mm/dd/yyyy format? 

Can you try adding 12/1/2022 date for one of the rows, just for testing? Also, need to check the full JSON code used by conditional formatting. It might be converting dates using some operators/functions in JSON.

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

[#3]

@ganeshsanap, good idea, I think that is what is happening. I need to work out why this is happening now, the regional settings for the site are correct. I guess the calculated column formula is doing something odd with the dates, although it should be formatting them as dd/MM/yyyy.

[#4 · Accepted solution]

Ah, fixed the issue. It was indeed the way that I had written the calculated column formula, to get around another issue I had wrapped the DATE function in TEXT, this was causing the dates to be read as MM/dd/yyyy rather than the correct dd/MM/yyyy. Thanks for the pointer @ganeshsanap