Check cell range and compare against date today()

ateleoh

I have looked through and tried to apply several solutions and none seem to  work with a range of cells.

I have a date/time field column under and have entries in several different collapsible fields.  I am in need of help creating a formula that will check all the cells in the range/in the collapsed range (are they children if there are multiple groups?) and indicate if any dates exists that are greater than today.  I can get anything to work EXCEPT the range.

Thank you in advance.

Paul Newcome

Could you provide more details?

 

Once it checks the cells, what do you want it to display?

 

 

ateleoh

Doesn’t particularly matter, but, “Expand” if true or “No Dates” if false would suffice.

A wish list request would be great to have it return the title of the entry closest to today. Example: We have a bunch of upcoming training dates that are assigned to different people.  Maybe the name of the person could be returned along with the nearest upcoming training date.  

Or the expand text along with highlighting the cell with the nearest date.

Many options, willing to get creative.

Paul Newcome

The easiest solution would be to use an IF statement and conditional formatting. 

 

=IF(COUNTIFS(CHILDREN([Date Column Name]@row), >TODAY()) > 0, "Expand", "No Dates")

 

If you put that in each parent row and change the column name to whatever the column is your dates are in, it will show "Expand" if there are dates greater than today and "No Dates" if there aren't. You could then use conditional formatting to highlight the cell if it has Expand in it, then use another conditional format to highlight the specific row with that date.

 

Your wish list is possible, but it would definitely require a little creativity. I can visualize it, but will have to actually do some testing to make sure the formulas and whatnot are correct. I have a busy day today, but I'll work on this when I can. No promises on a timeline though.

 

EDIT:

 

If you can provide some screenshots with the layout and column names I can tweak things to fit your sheet better and make explanations a little more clear for you. As it is, I have some formulas and whatnot built, but trying to describe to you what changes to make and where to put them won't make much sense.

ateleoh

This works great, thanks.  It looks like the [Column1]:[Column2] range I was trying to use wasn't the best solution.

It's unfortunate there does not seem to be a "now" function or datetime function that can be used with a single column.

Here is a screenshot of what I am doing.  We have a bunch of participants under multiple groups that we need to provide in-person support for a first visit.

I appreciate your help.

Paul Newcome

Where do the dates fit in?

ateleoh

Sorry for the delay.  I don't see notifications for replies to community posts as a function, but I could be wrong.  The dates fit in as such that I would like to get the min date, the next min date, and the max date for each area.

I get #INVALID COLUMN for the formula =MAX(Date1:Date9).  The 1st visit date column has the date property.  This could also work as IF building = XXX return min, min +1, max.  I hope this makes sense.

 

Paul Newcome

1.  Anywhere you are putting dates should be in a Date type column. Otherwise you will need FOURMULA(.........) + "" to have the date displayed as a text value. After that though, it can no longer be used as a date value in any other functions.

 

2.  Make sure the range of cells you specify does NOT include the cell that the formula is in. To help with this... You are already using hierarchies. Use the CHILDREN function to reference your ranges. If you are referencing children in another column on that same row, you would use CHILDREN([Column Name]@row).

 

3. You have to use actual column names in your formulas. So to find the earliest date, second earliest date, and the latest date you would use something along the lines of 

 

=MIN(CHILDREN([Column Name]@row))

=SMALL(CHILDREN([Column Name]@row), 2)

=MAX(CHILDREN([Column Name]@row)

 

These can all be combined into one cell in a few different ways depending on how exactly you wanted the dates to be displayed.

ateleoh

This is great.  Thank you!