Create Report from Two Sheets - looking for same task on the same day

Todd Michayluk

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.

Two Sheet Report.jpg

Any suggestions on what I need to do? Thanks in advance :)

Scott Peters

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:

Todd Michayluk

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.

Scott Peters

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:

1. =JOIN(COLLECT({A2 MME | Task}, {A2 MME | Start}, @cell <= Date@row, {A2 MME | End}, @cell >= Date@row), CHAR(10))
2. =JOIN(COLLECT({A1 Data | Task}, {A1 Data | Start}, @cell <= Date@row, {A1 Data | End}, @cell >= Date@row), CHAR(10))
3. =IF(AND([A2 MME Deployment]@row <> "", [A1 Data Services]@row <> ""), true, false)