How to track the project or task time in hours

@Nick Duxfield - Same goes for me.

I spent some time on this recently because our team needed to track time on projects.  We already utilized Planner, so I didn't want to give it up because of the familiarity.  Planner utilizes the O365 group mailbox (also utilized by Teams, Sharepoint, etc.) and effectively by default sends an email almost anytime a task is edited, but specifically so every time someone in the group adds a comment to a task.  This is where the we are entering the time - the comments of the task (in decimal hours format - just the number, no words).  So if you are using the comments section for anything else, this will not work, but otherwise it works flawlessly so far.  I will give an overview of what I did in hopes this can help someone else with the same problem we faced.

1.  Create an excel file in your Sharepoint site with a table that has specific column headers and give it a name like Task Timekeeping...I created the column headers "Owner", "Task", "Hours", "Complete", and "Date".

2.  You need the correct permissions to the O365 group mailbox.  OData operations need to be enabled.

3.  In MS Flow (now called MS Power Automate?) you will need to use the trigger for "When a new email arrives in a shared mailbox (V2)"

4.  After that, you can do some nifty formatting in Power Automate in order to parse the emails sent to the group mailbox:

4.1.  Use the "Convert Time Zone" action to convert the Received time from UTC to your time zone

4.2.  Extract the task name from the subject line of the email by using an expression in the Compose action - last(split(triggerBody()?['Subject'],'task')))

4.3.  Figure out where the line breaks are in the body of the email to record the hours from the user entered comment in planner.  (It will be at the top of the email)

4.3.1  Compose action expression (renamed 'Compose NewLine') - decodeUriComponent('%0A')

4.3.2  Compose action (renamed 'Compose Text') - Dynamic Content Body or Body Preview of email

4.3.3  Compose action expression (renamed 'Compose Find NewLines') - split(outputs('Compose_Text'), outputs('Compose_NewLine'))

4.3.4 Compose action expression - outputs('Compose_Find_NewLines')[0]

4.4  Find "completed" text in email body to determine if the task has been completed

4.4.1  Use Text Function - Find Text Position using Dynamic Content Body

4.4.2  Use Text Function - Substring using output of Find Text Position in previous step (Text = Body of email, Starting Position = Output of Find Text Position, Length = 9

5.  Add the information to your table in your Excel file by mapping the outputs from the above steps.  Use the "Add Row Into a Table" Action for Excel Online Business.

Now every time someone adds a comment to a Planner task, the spreadsheet will populate with the information.  Again, just make sure users are only putting in numbers in the comments field, otherwise it's going to get hairy reporting on on the total hours spent on a task.

You can use Power BI to actually visualize the results from the spreadsheet pretty nicely.

I know this a total back end, work around way of doing this - and it might not work for larger teams because the variable is that only numbers are entered into the comments section of the task - but for our team it works today.