Search text based upon date

PJay128 · 04/05/19

Hi Im struggle to put together a (what should be simple) statement to search a column (section) based on the month it was checked.

From the attached table i want to pull the data out of the sheet into another and record that the check has been completed for each month.

So the output would be in march electrical repairs, dust suppression, environmental checks were all completed however site buildings and pedestrian & Traffic were not.

The output in Feb would be vice versa..

Any help would be greatly appreciated :P

Andrée Starå · 04/05/19

Hi,

Does it have to be on another sheet or would it work with a report instead?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

PJay128 · 04/05/19

It could be used on a report yes however i need it for every month. My understanding of reports is that it will only so me data that is available. Therefore any checks which have not been completed will be missing.

I need to be able to spot the checks which have not been completed in the month. I don't need to know how many times its been done. Once a month will suffice.

It would be nice to make it visual using ticks etc but not a requirement. eg below

Paul Newcome · 04/05/19

You could use something along the lines of...

=IF(FIND([Section Column Name]@row, JOIN(COLLECT({Section Column Reference}, {Date Column Reference}, IFERROR(MONTH(@cell), 0) = ##), ",")) > 0, "Yes", "No")

Basically you are joining all of the cells from the master sheet that have the month of ##. You are then searching for the particular text in the [Section Name Column] in that string. If it finds the text then you will get your green check. If it does not, then you will get your red x.

Just replace the ## with whatever month number you are looking for.

Andrée Starå · 04/06/19

Reports can show everything that's on the sheet(s). The report could probably be set up to show what you need, and it could also be automated so that a report would send out each month if needed.

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Best,

Andrée

PJay128 · 04/07/19

Thankyou Paul never used the collect before however using that statement means i will pull the information from previous years.

I've tried to add the year to the argument but get an error.

=IF(FIND([Primary Column]@row, JOIN(COLLECT({Stourton Maintenance Range 1}, {Stourton Maintenance Range 2}, IFERROR(YEAR(@cell), 0) = 2019, {Stourton Maintenance Range 1}, {Stourton Maintenance Range 2}, IFERROR(MONTH(@cell), 0) = 11), ",")) > 0, "Yes", "No")

I dont want to pickup previous years checks just 2019's

Cheers

Paul Newcome · 04/08/19

Are you getting the #INCORRECT ARGUMENT error? It looks like your range/criteria syntax got a little mixed up.

=IF(FIND([Primary Column]@row, JOIN(COLLECT({Stourton Maintenance Range 1}, @{Stourton Maintenance Range 2}, IFERROR(YEAR(@cell), 0) = 2019, {Stourton Maintenance Range 1}, @{Stourton Maintenance Range 2}, IFERROR(MONTH(@cell), 0) = 11), ",")) > 0, "Yes", "No")

If you look at the above (your current formula) you will see that you have range then criteria which is correct. But then you have range, range, criteria. You have an extra range in there. I believe you may also have an extra closing parenthesis in there too After your JOIN statement is closed out. Give the below a try...

=IF(FIND([Primary Column]@row, JOIN(COLLECT({Stourton Maintenance Range 1}, {Stourton Maintenance Range 2}, AND(IFERROR(YEAR(@cell), 0) = 2019, IFERROR(MONTH(@cell), 0) = 11)), ",") > 0, "Yes", "No")

PJay128 · 04/08/19

Yes i was getting the Incorrect argument error and noticed i had the extra range but still getting the error.

I've tried as you suggested and still the same error

Paul Newcome · 04/08/19

What is in Range 1 and Range 2 for your cross sheet references? Are both ranges the same size?

PJay128 · 04/08/19

Yes i have chosen the column as the range on both so they are the same number of rows.

I have the dates as a system generated Column so ive added another column mirroring it but not a system one.

That is also throwing up the same error.

:S

Paul Newcome · 04/08/19

Try wrapping the formula you are using to mirror in a DATEONLY function.

=DATEONLY([System Generated Column Name])

PJay128 · 04/08/19

Same error although i might have done it wrong :S

The original formula worked fine but as soon as the year was added it went wrong.

Should the AND be between the month and year?

=IF(FIND([Primary Column]@row, JOIN(COLLECT({Stourton Maintenance Range 1}, {Stourton Maintenance Range 2}, IFERROR(YEAR(@cell), 0) = 2019, AND(IFERROR(MONTH(@cell), 0)) = 11)), ",") > 0, "Yes", "No")

Paul Newcome · 04/08/19

No. The AND should be wrapping the two sets of criteria.

I just noticed that I had missed a parenthesis. Try this corrected formula...

=IF(FIND([Primary Column]@row, JOIN(COLLECT({Stourton Maintenance Range 1}, {Stourton Maintenance Range 2}, AND(IFERROR(YEAR(@cell), 0) = 2019, IFERROR(MONTH(@cell), 0) = 11)), ",")) > 0, "Yes", "No")

PJay128 · 04/08/19

Yes the year works and the month.

Just need them together lol

Paul Newcome · 04/08/19

I realized I had missed a parenthesis in my original formula and edit my last post. Try that one...

PJay128 · 04/08/19

Was it a ) missing? sick of counting the bloody things

It works Paul your a saint!

Thankyou for your help

Search text based upon date

Comments

Categories