Highlight a row of cells because dates are not in a chronological order

[muna]

Does any know how to highlight a row of cells because dates are not in a chronological order? I have attached a sample spreadsheet which gives the requirements in a bit more detail.

 

[bosco_yip]

Select G5:N5 >> Conditional Formatting >> new rule >> Use a formula >> Edit rule :

=SUM(0+IFERROR((($D5:$N5-$C5:$M5)<0),0),0+IFERROR((($E5:$N5-$C5:$L5)<0),0))

>> click Format >> choose cell background color in : "red" >> OK

Regards
Bosco

 

[Peter Bartholomew]

Using named formulas, let the row of dates be 'dates' and the count 'n' refers to
= COLUMNS(dates)
The starting date of each period 'start' is one date short
= OFFSET( dates, 0, 0, 1, n-1 )
and the end date 'end' is offset one column to the right
= OFFSET( start, 0, 1 )
For a date to be valid, it must be a number, so 'validDate?' is the 'Boolean'
= ISNUMBER( start )
The key test for an error is provided by the Boolean 'outOforder?'
= (start>end)
Combining these into a single condition for conditional formatting
= OR( validDate? * outOfOrder? )

At this point I would tend to use a shape as a control to sort the range

Use dynamic buttons and shapes to create interactive Excel dashboards

Sometimes something more than text is called for if you are to get the attention of your users. This post suggests a way of introducing a button that colours or greys-out according to the situation.

www.spreadsheet1.com

but that would not work in the presence of the CANCELLED text.

 

[muna]

Select G5:N5 >> Conditional Formatting >> new rule >> Use a formula >> Edit rule :

=SUM(0+IFERROR((($D5:$N5-$C5:$M5)<0),0),0+IFERROR((($E5:$N5-$C5:$L5)<0),0))

>> click Format >> choose cell background color in : "red" >> OK

Regards
Bosco

Brillant.

Works perfectly.

Thank you for your help

 

[muna]

Using named formulas, let the row of dates be 'dates' and the count 'n' refers to
= COLUMNS(dates)
The starting date of each period 'start' is one date short
= OFFSET( dates, 0, 0, 1, n-1 )
and the end date 'end' is offset one column to the right
= OFFSET( start, 0, 1 )
For a date to be valid, it must be a number, so 'validDate?' is the 'Boolean'
= ISNUMBER( start )
The key test for an error is provided by the Boolean 'outOforder?'
= (start>end)
Combining these into a single condition for conditional formatting
= OR( validDate? * outOfOrder? )

At this point I would tend to use a shape as a control to sort the range

Use dynamic buttons and shapes to create interactive Excel dashboards

Sometimes something more than text is called for if you are to get the attention of your users. This post suggests a way of introducing a button that colours or greys-out according to the situation.

www.spreadsheet1.com

but that would not work in the presence of the CANCELLED text.

Really appreciate this.

Thank you!!

 

[p45cal]

You can also try this formula in conditional formats:

=TEXTJOIN(",",TRUE,IFERROR(SMALL($C5:$N5,ROW($A$1:$A$12)),""))<>TEXTJOIN(",",TRUE,IF(ISNUMBER($C5:$N5), $C5:$N5,""))

It copes with (ignores) blank cells as well as situations like:

where Dates 8, 9 & 10 are out of sequence.

 

[Peter Bartholomew]

@p45cal

Sorting the data is a really neat idea! Almost like cheating . I like the way TEXTJOIN sorts the misalignments by removing blanks.
I ran the concept through Office 365 insider and came up with filteredDates
= FILTER( Dates, ISNUMBER(Dates) )
and sortedDates
= SORT( filteredDates,,,1 )

The conditional formatting is then controlled by
= OR( filteredDates<>sortedDates )

[ That is assuming I have got the Boolean negation
= NOT( AND( filteredDates=sortedDates ) )
correct! ]

 

[p45cal]

I've not got SORT and FILTER, yet. I think it's due next month as I'm on the semi-annual channel for updates, so I'll give it a try then.