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
-
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
-
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:
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!