Create Report from Two Sheets - looking for same task on the same day
I have two sheets that both have the same type of tasks which are called "Build and Deploy". These tasks could be one day tasks or multiple day tasks. I want to know when these tasks from each of these sheets end up overlapping on the same day. I tried to make a report, and it works if the tasks start on the same day. But if they start on different days but overlap at some point because they are different durations the report doesn't group them together. I've attached a screenshot.
Any suggestions on what I need to do? Thanks in advance :)
Answers
-
Hello @Todd Michayluk - One idea would be to add a column to each sheet that uses the WEEKNUMBER formula, and use that to group tasks in your report:
-
Hi @Scott Peters thanks for your reply. Would this not just group these tasks by week then? How would I then be able to only display the ones that overlap on certain dates? If you could provide an example of how you would create this formula and the report that would be greatly appreciated. Don't have a lot of experience with formulas or reports. Thanks.
-
Hi @Todd Michayluk - That is correct, using Week Number for grouping is a fast solution, but it is not precise to the day. If you need to be more specific then I can think of two other ideas: 1) Design columns in each sheet that compare/check against the other sheet, or 2) Create a 3rd central sheet listing out all of the dates, and use Join/Collect to bring any overlapping tasks in. Here's an example of that 2nd idea:
You could then create a report over this new sheet if desired, filtered on the 'Overlap' column being checked. The formulas I used for this are:
- =JOIN(COLLECT({A2 MME | Task}, {A2 MME | Start}, @cell <= Date@row, {A2 MME | End}, @cell >= Date@row), CHAR(10))
- =JOIN(COLLECT({A1 Data | Task}, {A1 Data | Start}, @cell <= Date@row, {A1 Data | End}, @cell >= Date@row), CHAR(10))
- =IF(AND([A2 MME Deployment]@row <> "", [A1 Data Services]@row <> ""), true, false)
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives