Excel Gantt Chart → Add tasks to a Planner board → Copy over dates and assignment
The most challenging part of creating this Power Automate flow was transferring the date from the Tactical Implementation Plan (TIP) Gantt Chart to the Due Date on the task. Continue reading to learn about my solution.
1
The Need
The team I was on used Excel based Gantt Charts for project planning. They used to use Project, but found it to be a bit hard to work with, so they decided to try using Excel. The Excel was much easier to work with, but the downside is that there's no built in automation to create Planner tasks (Project includes this feature). My workaround was to take the easy-to-work with Excel and use Power Automate to create the tasks.
2
Building the basic flow
For this flow, building the steps in Power Automate was the easy part. All that was required was to List rows present in a table and then Apply to each value to create a task. I just had to make sure the formats were right for Power Automate. The Title only had to be in string format, but the Assignment needed to be in email format, and Due Date had a specific string format of yyyy-mm-ddThh:mm:ssZ. I knew this would be a bit tricky, but it was time to start working on the table in Excel!
3
Setting up the table
I had to convert the Gantt chart to a table format for Power Automate to be able to work with it, so I first created a separate sheet with a simple table. To start, I needed: Title, Assignment, and Due Date. It was simple to pull in the Title since that only needed to be in string format. However, the assignment must be in email format and the due dates had to be pulled in from cells that indicated the date from a reference row at the top.
4
Using formulas to pull in due dates and emails
To convert the waterfall format from the Gantt chart to a single cell with dates and to match the assignment with email address, I used an index match combination to find the cell that lined up with the matching value. When I first tried to run the flow, I was getting an error for the date. It turned out that Power Automate needs the date in a very specific string format, so I just had to account for that in my formula.
5
Testing and Troubleshooting
I noticed a weird issue when importing dates, the Due Date was being set as the day prior. After some troubleshooting, I realized when the dates were importing, the output was concatenating T00:00:00Z to the end of the dates. For some reason, this was causing the due date on the card to set as the day prior. To fix this, I added a concatenation of T10:00:00Z to add time to the date and see if that fixed the problem, which it did!
Here are the final products:
