Home > Blog

Extract dates from a Visio diagram

Sometimes there will be an existing summary "plan on a page" (POAP) in Visio which you want to replace with a SummaryPro POAP due to the increased ease and accuracy of the updating.  With a simple POAP it is easy enough to recreate manually however with a complex one or if you have many to do it is nice to be able to automate the process.

This blog will walk you through the steps of extracting the positional data from Visio and then calculating the dates that each milestone or task are showing so that you can use this in SummaryPro.  

For this example we will use a simple POAP:

Simple POAP

To extract the positional data for each shape on the page click on the "Shape Reports" in the Reports section of the Review ribbon.

shape reports button

If this is the first time that you have done this you will need to set up a new report.  To do this click on"New" in the dialogue that comes up

select new

Then choose "shapes on the current page"

shapes on the current page

You then need to choose which attributes to include in the report:

  • Displayed text
  • Shape master
  • Shape ID
  • Shape name
  • Width
  • X location
  • Y location

choose data to display

Give the report a title and choose Next.  On the next screen give the report a name (which can be the same as the title if you want) and if needed a description of what it does.

Then you will be returned to the Reports dialogue where you choose to run your new report.

You will be asked how you want to output the report - choose Excel.

choose excel

Now an excel report will open displaying the positional data for all shapes on the page.  If you have a very complex report you can spend some time removing the items which you are not interested in, however, this isn't necessary.

position data report

Now we need to calculate what the position on the page means in terms of dates.  To do this we need to enter a series of calculations based on the earliest date and the last date in the report.  Eventually, you will end up with the sheet below,; the following steps will talk you through the process of creating this sheet.  You can save this sheet to reuse with future reports.

finished calculation sheet

First, we need to work out how many mm each day is on the source POAP.  To do this you will enter the following calculations and then name some of the cells to make them easier to reference in the main calculations.


In each of the cells below copy the calculations:

T2 - enter the first date in the source POAP, in this case, 1st Jan 2022

T3 - enter the last date in the source POAP, in this case, 31st May 2022

V2 =MIN(M:M) - i.e. the left-most shape

V3 =MAX(N:N) - I..e the right-most shape

T5 =IF(T4="yes",T3-T2,NETWORKDAYS(T2,T3)) - works out the number of days covered by the date range

U6 =U3-U2 - how many mm are there between the left and right most points

U7 =T5/U6 = how many mm is each day

 To make it easier to reference these numbers in the main calculation set you should name some of the ranges as below.  To do this select the cell and then type the new name in the address box at the top left.

named cells

Now you can use the mm/day calculation to work out the dates for each task or milestone.  Note that you need to tell the calculation if an item is a milestone as there is no way for Visio or Excel to know.  You do this in column I.

main calculations Enter the following calculations into J3 to P3 and then copy down to cover the data:

J3 =VALUE(TRIM(LEFT(E3,FIND(" ",E3)-1)))

K3 =VALUE(TRIM(LEFT(F3,FIND(" ",F3)-1)))

L3 =VALUE(TRIM(LEFT(G3,FIND(" ",G3)-1)))

M3 =K3-(J3/2)

N3 =K3+(J3/2)

O3 =IF(I3="yes",start+((K3-Start_X)*day_inc)+IF(K3=Start_X,0,1),start+((M3-Start_X)*day_inc)+IF(M3=Start_X,0,1))

P3 =IF(I3="yes",O3,start+((N3-Start_X)*day_inc))

Now you have the information needed to set up a SummaryPro Plan on a Page thatwill be a lot easier and more accurate to maintain going forwards.

Miles Goodchild

Miles Goodchild has been a Program and Programme Planner, PMO lead and Project Manger since 1998.  He enjoys using MS Office, especially Project, Excel and Visio to make life easier and simpler.  In the course of this he created SummaryPro.  In this blog he shares some of the tips and techniques he has learnt over the years in the hope that they will be useful to you.