Find the earliest outstanding date with parameters

megan.griffiths

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

Find more posts tagged with

Accepted answers

Mike Raposo

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

All comments

Javier Arbelaez

Hey @megan.griffiths , Can you give an example of your data if possible?

megan.griffiths

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

Mike Raposo

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

megan.griffiths

Ahhhh @Mike Raposo you are a star! Thank you so much! You were right, it was a date column!

Thanks again,

Meg

Javier Arbelaez

@megan.griffiths are there multiple rows for the same client or is every row unique?

megan.griffiths

Hi Javier,

All rows were unique, but Mike has sorted it now, thank you!

Meg

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.