Find the earliest outstanding date with parameters
Hi all,
I am trying to create a formula, that finds the earliest date in {Date Received}, if the {Date Processed} is blank.
I have tried this formula, but it comes back as #Invalid Column Value:
=(MIN(COLLECT({Date Received}, {Date Processed}, "")))
I then added an IFERROR as below, but that returns no value at all.
=IFERROR("", MIN(COLLECT({Date Received}, {Date Processed}, "")))
Can someone help please?
Thanks,
Meg
Best Answer
-
Hi Megan,
Quick question, you're writing this formula in a different sheet that's referencing the data in the sheet from the screenshot correct?
If so, you'll need to set the column you're writing the formula in to a Date type column, that should resolve the error, and then you're formula should work as intended.
Hope this helps! Let me know if you have any questions or are still having additional issues.
Best,
Mike
Answers
-
Hey @megan.griffiths , Can you give an example of your data if possible?
-
Hi Javier,
I cant give a live example as it is all client data, but I have a sheet that counts the fee taken from a client. The current SLA for processing New Business is 2 days, so I basically need to find the oldest received piece of New Business, that has not yet been processed.
Therefore, id need to find the oldest date in the 'Date Received' column, if the 'Date Processed' was blank (e.g. not yet processed).
Does this help at all? If not, I can do a mock up that doesn't contain client data?
Thanks,
Meg
-
Hi Megan,
Quick question, you're writing this formula in a different sheet that's referencing the data in the sheet from the screenshot correct?
If so, you'll need to set the column you're writing the formula in to a Date type column, that should resolve the error, and then you're formula should work as intended.
Hope this helps! Let me know if you have any questions or are still having additional issues.
Best,
Mike
-
Ahhhh @Mike Raposo you are a star! Thank you so much! You were right, it was a date column!
Thanks again,
Meg
-
@megan.griffiths are there multiple rows for the same client or is every row unique?
-
Hi Javier,
All rows were unique, but Mike has sorted it now, thank you!
Meg
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!