Formula for a future meeting date (after today)

Gerry McRobert

Good morning - I have a series of columns with scheduled "future" meeting dates.  I want to populate one column with the date of the next scheduled meeting.  Have tried importing the formula which works in Excel over, but Smartsheet keeps on saying "INVALIDOPERATION.  Help please !

Brett Evans

The MIN function is your friend here...

https://app.smartsheet.com/b/publish?EQBCT=f5e1a4bfeedb45549bc82916ad2aae9b

 

Gerry McRobert

Tried your suggestion, and now get the following message.  Have screenshot my column names and the formula entered.

 

Gerry McRobert

Sorry - here is the screenshot

 

 

J. Craig Williams

This should work:

 

=IFERROR(IF(MIN([Column7]12:[Column9]12) > TODAY(), MIN([Column7]12:[Column9]12), "ADJUST"), "Likely no dates")

 

for row 12.

I think you just missed a paranthesis at the end - but maybe not.

 

Column6 has to be a Date type column because when the IF is true, you want to return a date.

If there is no dates at all, you'll get an error - so I wrapped it in a nice IFERROR.

 

But I still think the #UNPARSEABLE was due to a missing ")"

 

Craig 

Gerry McRobert

Craig,

 

Thanks for the suggestion - unfortunately did not work..  The spreadsheet I am working on has a series of cells that may or may not contain a date (meeting dates). Each column is a month and there are some months where meetings will not take place.  The first column is "Next Meeting Date" so I want the system to calculate the next meeting after today.  If the formula sees a date, it will insert the next one in "Next Meeting Date".  If the column is empty, then the formula should skip it.

 

I have the formula working in Excel, but it will not import over to Smartsheet.

 

Anyone else with a suggestion?

 

 

Brett Evans

Can you publish a sanitized copy your sheet so we can see it?   It's hard to debug without digging in.

Gerry McRobert

Spreadsheet published:

 

https://app.smartsheet.com/b/publish?EQBCT=de5a2da1752648debe744e0d75db7ac4

 

Appreciate the assistance on this.

Brett Evans

I am not sure what you are trying to do with this formula

=LARGE($[CM - JANUARY]1:$[AGM / SGM Date]1,COUNTIF($[CM - JANUARY]1:$[AGM / SGM Date]1,">="&TODAY()))

 

LARGE is not a SmartSheet function also you have you "greater than or = to" contained in quotes, if you are trying to make a comparison that will not work.  Can you explain again wht you are trying to accomplish or attach the working excel sheet to the published sheet.

J. Craig Williams

It won't work in Smartsheet, if I understand what you are trying to do 

 

evaluate all dates for defined meetings and compare them to today's date.

the one that is smallest but still positive is next.

 

MATCH (the mostly candidate here) does not take dates as an input.

If it did, your data needs to be in either ascending or descending order - but it isn't.

 

meeting_date = max of all dates in the range

for each date in the range

  if date is greater than today and

      date is less than meeting_date, then meeting date is equal to  date

 

The @cell function does not work for all functions that accept a range.

 

Without lots of extra and unnessary columns or rows -- I don't see a solution.

 

I might be wrong.

 

Craig

 

Gerry McRobert

Brett,

 

I appreciate the "=large" formula does not work in Smartsheet - I simply uploaded the spreadsheet as an example.

 

What I am trying to do is have a single column that states what the next meeting date is.  There is a 15 month series of columns, between January - March (+1 year) that we insert meeting dates within.  The formula should be able to select the next inputted meeting date.  If the fields are blank, then they are skipped, resulting in a blank "Next Meeting Date".

 

Gerry

J. Craig Williams

I stand by my assessment.

Your [Previous Year AGM] column results in a non-ordered list, at least based on the formula you tried to use.

 

Now you are saying you want this instead

 

meeting_date = max of all dates in the range

for each date in the range

  if date is greater than today or blank and

      date is less than meeting_date, then meeting date is equal to  date

      and exit

 

Still not possible.

 

Craig

CourtneyL

I'm not sure if this would help anyone since its an older topic, but I was able to figure out how to generate next next future due date in a series for a schedule document of mine. I only had to add one extra formula column.

So I have a series of dates on child lines under a parent item (The blue row in the image), each child line is the next delivery date. I wanted the next future delivery date (The yellow row in the image) to populate on the parent line, so I wouldn't have to expand child lines to see.

I created one extra column called I called "Next Due Date" and put the following formula in there:

=IF(TODAY() - [Due Date]@row < 0, (TODAY() - [Due Date]@row), "")

Then on the parent row I placed this formula:

=MAX(CHILDREN())

This gave me the difference value for the next upcoming due date

Then in my Parent Due Date cell I combined the TODAY to the Value in the parent Next Due Date cell.

=TODAY() - [Next Due Date]@row

 

Michael Gricoski

This will find the next future date (or today) in a list of dates.

=MIN(COLLECT([Start Date]:[Start Date], [Start Date]:[Start Date], >=TODAY()))

 

Start Date is your column of dates that you wish to find the next earliest date after or on today.

Place the formula in a date column.

James Vandezande

Anybody know if I can use this MIN(COLLECT(... method inside a MATCH formula?

I'm trying to use this as a means to report a task name that is upcoming in date.