Function to combine different dates

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!

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • 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)

  • 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))))

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!