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

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

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