Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Formula for a future meeting date (after today)
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 !
Comments
-
The MIN function is your friend here...
https://app.smartsheet.com/b/publish?EQBCT=f5e1a4bfeedb45549bc82916ad2aae9b
-
Tried your suggestion, and now get the following message. Have screenshot my column names and the formula entered.
-
Sorry - here is the screenshot
-
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
-
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?
-
Can you publish a sanitized copy your sheet so we can see it? It's hard to debug without digging in.
-
Spreadsheet published:
https://app.smartsheet.com/b/publish?EQBCT=de5a2da1752648debe744e0d75db7ac4
Appreciate the assistance on this.
-
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.
-
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
-
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
-
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
-
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
-
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.
-
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.
