SYNCHRO Tips & Tricks: Set Task Start Date based on assigned Earliest Resource Status Date

Resource Statuses allows you to record/visualize and track the status of each Resource with a Date stamp for each Status update. It can then be viewed in the 3D window in SYNCHRO PRO and/or in the mobile SITE app. You can also use these statuses to quantify actual quantities, define rule of credit, and progress task % complete as well.

Thomas Schimpf, a fellow consultant, and a SYNCHRO expert talks about using Resource Statuses, both within SYNCHRO Pro and with SYNCHRO SITE in one of the sessions of SYNCHRO Office Hours.

There can be multiple Resources having a status - indicating the work having been started - assigned to a single Task, and each Resource may have a different start date. In the example below, there are multiple Piles assigned to the Task ‘T00990 – Piles 1’ that have different ‘In progress status dates, and a few having additional status, ‘Complete’. There’s no easy way of knowing when work on the first Pile, assigned to the Pile 1 task, had started, and would have to manually sort through the Status History (right-click on Resources window header > Customize column > Status > Status History) to find the date. This is a daunting task, esp. if we are dealing with numerous amounts of Resources and Tasks.

  

However, there’s a nifty workaround using the very convenient interoperability with Microsoft Excel!

All the SYNCHRO Pro data can be exported out to an Excel workbook. You can make bulk updates on properties not easily editable within Pro; reference and input information from external data sources (think Cost Codes); generate Reports and Graphs or use it with Power BI for creating Dashboards and more advanced Data Analysis (review the PBI information here).

Not to stray off the topic of discussion, you can also find the earliest status date from all the Resources assigned to each Task using Excel, and bring that back to SYNCHRO Pro.  

Step 1:

Go to File > Export > Microsoft Excel and select which data you want to export as individual sheets -

  

Note: To export additional data over the basic template requirements, you will have to make those columns visible within the Task/Resource/3D Objects windows. For this example, I have displayed Status and Actual Start columns within Task window (Customize Column).

 

Step 2:

Once in Excel, go to Assignment sheet, which has a list of all the Resources and the Tasks they are assigned to. Now create a new column, EarliestStatusDate and enter the following formula on the first row –

= IF(MINIFS(ResourceStatuses!$F:$F,ResourceStatuses!$A:$A, Assignments!$B2)=0,"",MINIFS(ResourceStatuses!$F:$F,ResourceStatuses!$A:$A, Assignments!$B2))

This will search for all the status dates within the ResourceStatuses sheet belonging to the ResourceID in the related row and return the earliest date. You can copy this formula for the rest of the rows to get the earliest date for all the Resources within the project.

Note: The formula works on an internal array and you need to press Ctrl+Shift+Enter to run it.

 

 

To understand the formula and its workings in more detail, lets break it down to smaller components - 

MINIFS(ResourceStatuses!$F:$F,ResourceStatuses!$A:$A, Assignments!$B2)

“The MINIFS function returns the minimum value among cells specified by a given set of conditions or criteria.”

Looking at the sheet ResourceStatuses, it has the list of all the Resources and the Statuses that have been applied to it, with Column F having the StatusDate. Following the syntax in the MINIFS formula, ResourceStatuses!$F:$F, will find the minimum value, i.e. the earliest status date. The criteria defined by ResourceStatuses!$A:$A looks for all the values in the ResourceID in Column A to match the value defined by Assignments!$B2, the selected row having the same ResourceID in Column B.

Following the Resource ‘SR00014060 - Pile 1’, you can see in the image below that it has two statuses – ‘In progress’ and ‘Complete’, dated 4/16/2015 and 4/18/2015 respectively. Running our formula thus gives us the earliest date of 4/15/2015, as shown in the above image.

  

 

The other part of the formula is the IF(logical test, value if true, value if false ) statement. If MINIFS logical test doesn’t find any matching value and gives ‘0’ as a result, then it will be replaced with a blank “”, otherwise, it will give the matching earliest date.

 Note: Just like how we used MINIFS to get the earliest (MIN) date, we can similarly use MAXIFS to get the latest (MAX) date. This can be potentially used to set the Task Finish Date.

= IF(MAXIFS(ResourceStatuses!$F:$F,ResourceStatuses!$A:$A, Assignments!$B2)=0,"",MAXIFS(ResourceStatuses!$F:$F,ResourceStatuses!$A:$A, Assignments!$B2))

 

Step 3:

Now that we know the earliest status date for each Resource, we still need to find the earliest start date for all the Resources assigned to a Task. We will use the Task sheet for this, using a very similar formula as above. You can paste the following formula in the ActualStart column -  

=IF(MINIFS(Assignments!$L:$L, Assignments!$A:$A, Tasks!$A18)=0,"",MINIFS(Assignments!$L:$L, Assignments!$A:$A, Tasks!$A18))

The above formula looks for all the ResourceIDs assigned to the TaskID in the selected row, and get the earliest date from our previously created column EarliestStatusDate in the Assignment sheet. Following our example of the Task ‘ST00990 – Piles 1’, we get the earliest status date, i.e. the ActualStart date as 4/15/2015.

 Note: To successfully import the updated ActualStart date back into SYNCHRO Pro, you will have to change the Task Status from Planned to Started. I will let you figure out creative ways of doing this!

  

 

Step 4:

You can import the changed from the edited Excel workbook back to SYNCHRO Pro, by going to File > Import > Microsoft Excel.

You will get the Warning:  The column [EarliestStatusDate] in the [Assignments] table was not recognized. Data from this column was ignored. Since the column we created doesn’t conform to the set standard template, the data will be ignored, however, updates made to the ActualStart column will be imported.

     

  

This is one of the ways you can use various functionalities and workarounds to achieve something that may not be easily possible using the GUI within SYNCHRO Pro. I am sure the SYNCHROnians of the world will provide more innovative processes to achieve something similar in a more efficient manner.

I and am looking forward to continuing the conversation!