Highlight next date

allison.bastone24911

I can't find a way to conditionally format a date field to highlight the closest dates only.

For example, if sheet has:

08/30/2018

08/30/2018

09/04/2018

09/04/2018

09/13/2018

I want to automatically highlight the 8/30 dates.  They will fall off the sheet after the 30th, so then the sheet should highlight 09/04 rows.

Is there a way to do that?

Thanks

 

J. Craig Williams

How do they "fall off" the sheet?

This sounds suspiciously like the previous post here:

https://community.smartsheet.com/discussion/minchildren-not-consistently-updating-parent-row

but that has a different author.

(back in a few minutes)

Craig

allison.bastone24911

They "fall off" because they then fall outside the Jira query.   

The date field that is populated is manual, not formula driven, not related to a parent/child relationship, unlinke the other card you reference.  

Paul Newcome

You could use a helper checkbox column and enter

 

=IF([Date Column Name]@row = MIN([Date Column Name]:[Date Column Name]), 1)

 

This will look for the earliest date in the column. If the date in that row matches the earliest date, it will check the box.

 

You could then base your conditional formatting on whether or not the box is checked.

J. Craig Williams

Depending on how your data is arranged, you will need either one or two new columns.

The first would be a Date column and is the optional one. You need somewhere to determine what the date you are looking for is. If you are looking for the date in the whole column, the date to match must be in a different column - either one that has a cell to 'repurpose' or a new column. I dislike columns that have only one (or a few) cells that are used but sometimes it is necessary.

If your dates are 'falling off', then this would work (my column is [Request Date])

=MIN([Request Date]:[Request Date])

but in most use cases I can image, you'll want to weed out things like Done or Canceled or other such things. But for this example, it should suffice.

The second column is how you check for a match between the date in the rows and the date you have captured.

In my example, I used a Checkbox and this formula:

=[Request Date]@row = MIn$23

where my captured date is in the [Min] column on row 23.

This is functionally the same as

=IF([Request Date]@row = MIn$23,1,0)

That formula is copied to the rest of the column.

WIth a column showing the match, set the Conditional Formatting as desired.

I hope that helps.

I'll add this to my list of things to expand on in posts to my website.

Craig

Paul Newcome

As usual... I go for the combo formula (to avoid that column with only one cell being used), and you break it out. Yet we still end up doing pretty much the same thing. Haha. I'm beginning to think there's some kind of conspiracy here... 

 

Either way... Having multiple options to achieve the same goal is always a good thing. 

J. Craig Williams

I saw that. 

What I REALLY want is some header or sheet meta data for these sort of things.

If I had a 'short' Sheet, I might be tempted to use the no extra column solution. But I don't have a way to know what defines 'short' one method is better than another.

I want that too, but not as much as the meta data.

Craig

 

allison.bastone24911

That worked!  Thank you both!

J. Craig Williams

That's nice. You are welcome.

Craig