Function to combine different dates

Desertbird

Hi, I am working with timecard data, and trying to find a way to show how many days someone called out for a specific reason. The problem is that there are sometimes multiple rows per day.

It looks similar to the table below (blank spaces are worked hours). For example, if I wanted to know how many days Bob had FMLA, it should show 1, not 2 since they are the same date. I can't do this as a report because I still need the data for other parts of this project.

Any help would be greatly appreciated!

Genevieve P.

Hi @Desertbird

You can use a DISTINCT Function to count how many times a unique value appears in your Date column, so it only counts one day once (even if it's repeated). Then you'll want to use a COLLECT Function to add your other criteria (e.g. "Bob" and "FMLA"), to act as a filter.

Try something like this:

=COUNT(DISTINCT(COLLECT(Date:Date, Name:Name, "Bob", Comment:Comment, CONTAINS("FMLA", @cell))))

Or if you're tracking this in a metric sheet with cross-sheet formulas:

=COUNT(DISTINCT(COLLECT({Date Column}, {Name Column}, "Bob", {Comment Column}, CONTAINS("FMLA", @cell))))

Let me know if this makes sense and will work for you!

Cheers,

Genevieve

Genevieve P.

Hi @Desertbird

You can use a DISTINCT Function to count how many times a unique value appears in your Date column, so it only counts one day once (even if it's repeated). Then you'll want to use a COLLECT Function to add your other criteria (e.g. "Bob" and "FMLA"), to act as a filter.

Try something like this:

=COUNT(DISTINCT(COLLECT(Date:Date, Name:Name, "Bob", Comment:Comment, CONTAINS("FMLA", @cell))))

Or if you're tracking this in a metric sheet with cross-sheet formulas:

=COUNT(DISTINCT(COLLECT({Date Column}, {Name Column}, "Bob", {Comment Column}, CONTAINS("FMLA", @cell))))

Let me know if this makes sense and will work for you!

Cheers,

Genevieve

Desertbird

Thanks for the response, it worked! If I were to add one other condition (under 30 days) would it be another criteria like:

(date column), AND(@cell <= TODAY(), @cell > TODAY(-30)

Genevieve P.

Hi @Desertbird

Yes! 😊

I would probably write it like this, but I believe your way should work, too:

=COUNT(DISTINCT(COLLECT({Date Column}, {Date Column}, @cell <= TODAY(), {Date Column}, @cell > TODAY(-30), {Name Column}, "Bob", {Comment Column}, CONTAINS("FMLA", @cell))))