Making Timelines with Dates Earlier than 1900 in Visio

I was asked recently about making Visio timelines with dates earlier than 1900, which is the start of time as far as Microsoft Office products are concerned, so I thought I’d try to adapt one of my approaches used on other projects. In this case, I am assuming that the data is coming from an Excel worksheet, and I will use the Link Data to Shapes feature in Visio Professional/Premium. Visio Standard users can still make the timelines manually, or could attempt to use the older Database Wizard.

Create the data source in Excel

The first decision I made was not to use date fields in Excel or Visio because of the 1st Jan 1900 is the start of everything issue. Therefore I created a table in a worksheet that splits the Start and End dates in Year, Month and Day columns:

image

In this example I have also included a column for the height of the timeline event shape, and a thematic area label (these properties were requested by the original Visio questioner). You simply make the End date the same as the Start date, if the event is just a moment in time.

Create an Event shape

I linked the Excel worksheet to Visio using the Link Data to Shapes feature, then I drew a rectangle in Visio and linked one of the rows to it. This automatically created Shape Data rows. I edited the Data Graphics so that only the Name property is displayed in the centre of the rectangle:

image

The application of Data Graphics automatically adds some User-defined Cells, and the Link Data to Shapes feature adds Shape Data rows:

image

My intention is to use the page width as the timeline, where the left edge represents the start of the required period, and the right edge is the end of the period. Therefore the event shapes should automatically position themselves horizontally within this timeline, beginning at its start date, and its width representing its duration.

Setting up the page

Firstly, I changed the settings for my page so that it does not automatically re-size:

SNAGHTML58d0347

So, in order to define the width of the page as a period of time, I added some Shape Data rows to the page for the Start and End dates. Again, I did not use the DateTime Shape Data type as it cannot display dates before 1900, however I did add a couple of text fields that merely format the dates as required:

image

I also added some User-defined Cells to assist in the computations, and enhanced the formulas in some of the Shape Data rows:

image

User-defined Cells

The User-defined cells are used to add some smartness to the shape, so I’ll first list them, and then discuss some of the more interesting bits:

Row Name Formula Description
IsStartYearALeapYear MODULUS(Prop.StartYear,4)=0 TRUE if Start Year is a leap year
IsEndYearALeapYear MODULUS(Prop.EndYear,4)=0 TRUE if End Year is a leap year
Months “Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec” Ordered list of month names
MonthDays “01;02;03;04;05;06;07;08;09;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28” Base month days list
StartMonthIdx LOOKUP(Prop.StartMonth,Prop.StartMonth.Format) Zero-based index of Start Month
EndMonthIdx LOOKUP(Prop.EndMonth,Prop.EndMonth.Format) Zero-based index of End Month
StartMonthTrigger DEPENDSON(Prop.StartDay)+SETF(GetRef(Prop.StartDay.Format),”=”””&User.MonthDays&IF(LOOKUP(User.StartMonthIdx,”8;3;5;10″)>-1,”;29;30″,IF(LOOKUP(User.StartMonthIdx,”0;2;4;6;7;9;11″)>-1,”;29;30;31″,IF(User.IsStartYearALeapYear,”;29″,””)))&””””) Forces update of the Start Day Format when the Start Month is changed
EndMonthTrigger DEPENDSON(Prop.EndDay)+SETF(GetRef(Prop.EndDay.Format),”=”””&User.MonthDays&IF(LOOKUP(User.EndMonthIdx,”8;3;5;10″)>-1,”;29;30″,IF(LOOKUP(User.EndMonthIdx,”0;2;4;6;7;9;11″)>-1,”;29;30;31″,IF(User.IsEndYearALeapYear,”;29″,””)))&””””) Forces update of the End Day Format when the End Month is changed
DaysPerMonth “31;28;31;30;31;30;31;31;30;31;30;31” Ordered list of number of days in each month (non-leap year)
StartDayOfYear INT(Prop.StartDay)+IF(User.StartMonthIdx>0,INDEX(0,User.DaysPerMonth),0)+IF(User.StartMonthIdx>1,INDEX(1,User.DaysPerMonth)+ABS(User.IsStartYearALeapYear),0)+IF(User.StartMonthIdx>2,INDEX(2,User.DaysPerMonth),0)+IF(User.StartMonthIdx>3,INDEX(3,User.DaysPerMonth),0)+IF(User.StartMonthIdx>4,INDEX(4,User.DaysPerMonth),0)+IF(User.StartMonthIdx>5,INDEX(5,User.DaysPerMonth),0)+IF(User.StartMonthIdx>6,INDEX(6,User.DaysPerMonth),0)+IF(User.StartMonthIdx>7,INDEX(7,User.DaysPerMonth),0)+IF(User.StartMonthIdx>8,INDEX(8,User.DaysPerMonth),0)+IF(User.StartMonthIdx>9,INDEX(9,User.DaysPerMonth),0)+IF(User.StartMonthIdx>10,INDEX(10,User.DaysPerMonth),0) Day of year of Start Date
EndDayOfYear INT(Prop.EndDay)+IF(User.EndMonthIdx>0,INDEX(0,User.DaysPerMonth),0)+IF(User.EndMonthIdx>1,INDEX(1,User.DaysPerMonth)+ABS(User.IsEndYearALeapYear),0)+IF(User.EndMonthIdx>2,INDEX(2,User.DaysPerMonth),0)+IF(User.EndMonthIdx>3,INDEX(3,User.DaysPerMonth),0)+IF(User.EndMonthIdx>4,INDEX(4,User.DaysPerMonth),0)+IF(User.EndMonthIdx>5,INDEX(5,User.DaysPerMonth),0)+IF(User.EndMonthIdx>6,INDEX(6,User.DaysPerMonth),0)+IF(User.EndMonthIdx>7,INDEX(7,User.DaysPerMonth),0)+IF(User.EndMonthIdx>8,INDEX(8,User.DaysPerMonth),0)+IF(User.EndMonthIdx>9,INDEX(9,User.DaysPerMonth),0)+IF(User.EndMonthIdx>10,INDEX(10,User.DaysPerMonth),0) Day of year of End Date
DurationDays ((Prop.EndYear-Prop.StartYear)*365)+(User.EndDayOfYear-User.StartDayOfYear)+INT((Prop.EndYear-Prop.StartYear)/4) Days Duration between Start and End Date
WidthPerDay PageWidth/User.DurationDays Width of page per day
msvDGCalloutGap DrawingScale/PageScale*0.0625 Added by Data Graphics

I had to come up with a couple of trigger actions to ensure that the correct number of days are displayed for the selected month.

image

The StartMonthTrigger and EndMonthTrigger formulas will update the format cells of the Prop.StartDay and Prop.EndDay Shape Data rows respectively, with a drop-down list of the correct number of days for the year and selected month. The DEPENDSON(…) function ensures that the list is recalculated in time for you to be able to select a value from the drop-down list.

I used concatenated IF() functions in the StartDayOfYear and EndDayOfYear cells, rather than nested IF() functions, because I have previously had nested IF() functions failing to evaluate when had more than 10 levels of nesting.

The DurationDays cell calculates the number of days, with an adjustment for leap years, between the Start and End dates.

All of the previous cells are leading up to be able to calculate the WidthPerDay cell value, as this will be used to horizontally position and size each of the Event shapes.

Shape Data

I created a few Shape Data rows, as per the table below.

Name Label Type Format Value
StartYear “Start Year” 2   <yyyy>
StartMonth “Start Month” 1 User.Months <select from list>
StartDay “Start Day” 1 <set by User.StartMonthTrigger formula> <select from list>
EndYear “End Year” 2   <yyyy>
EndMonth “End Month” 1 User.Months <select from list>
EndDay “End Day” 1 <set by User.EndMonthTrigger formula> <select from list>
StartDate “Start Date” 0   GUARD(Prop.StartDay&” “&Prop.StartMonth&” “&Prop.StartYear)
EndDate “End Date” 0   GUARD(Prop.EndDay&” “&Prop.EndMonth&” “&Prop.EndYear)

The StartDate and EndDate rows are provided purely for use in labelling, and are made read only with the GUARD(…) function.

Enhancing the Event shape

Now that we have set-up our page, we can return to the Event shape to complete it.

Firstly, I unlinked the shape from the data because I have all of the Shape Data rows that I need. You can do that from either the right-mouse menu of the row in the External Data window or from the right-mouse menu of the shape itself.

User-defined Cells

Name Formula Description
StartMonthIdx LOOKUP(Prop._VisDM_Start_Month,Prop._VisDM_Start_Month.Format) Zero-based index of the Start Month
EndMonthIdx LOOKUP(Prop._VisDM_End_Month,Prop._VisDM_End_Month.Format) Zero-based index of the End Month
IsStartYearALeapYear MODULUS(Prop._VisDM_Start_Year,4)=0 TRUE if Start Year is a leap year
IsEndYearALeapYear MODULUS(Prop._VisDM_End_Year,4)=0 TRUE if End Year is a leap year
StartDayOfYear INT(Prop._VisDM_Start_Day)+IF(User.StartMonthIdx>0,INDEX(0,ThePage!User.DaysPerMonth),0)+IF(User.StartMonthIdx>1,INDEX(1,ThePage!User.DaysPerMonth)+ABS(User.IsStartYearALeapYear),0)+IF(User.StartMonthIdx>2,INDEX(2,ThePage!User.DaysPerMonth),0)+IF(User.StartMonthIdx>3,INDEX(3,ThePage!User.DaysPerMonth),0)+IF(User.StartMonthIdx>4,INDEX(4,ThePage!User.DaysPerMonth),0)+IF(User.StartMonthIdx>5,INDEX(5,ThePage!User.DaysPerMonth),0)+IF(User.StartMonthIdx>6,INDEX(6,ThePage!User.DaysPerMonth),0)+IF(User.StartMonthIdx>7,INDEX(7,ThePage!User.DaysPerMonth),0)+IF(User.StartMonthIdx>8,INDEX(8,ThePage!User.DaysPerMonth),0)+IF(User.StartMonthIdx>9,INDEX(9,ThePage!User.DaysPerMonth),0)+IF(User.StartMonthIdx>10,INDEX(10,ThePage!User.DaysPerMonth),0) Number of days that the Start Day is from the beginning of the year
EndDayOfYear INT(Prop._VisDM_End_Day)+IF(User.EndMonthIdx>0,INDEX(0,ThePage!User.DaysPerMonth),0)+IF(User.EndMonthIdx>1,INDEX(1,ThePage!User.DaysPerMonth)+ABS(User.IsEndYearALeapYear),0)+IF(User.EndMonthIdx>2,INDEX(2,ThePage!User.DaysPerMonth),0)+IF(User.EndMonthIdx>3,INDEX(3,ThePage!User.DaysPerMonth),0)+IF(User.EndMonthIdx>4,INDEX(4,ThePage!User.DaysPerMonth),0)+IF(User.EndMonthIdx>5,INDEX(5,ThePage!User.DaysPerMonth),0)+IF(User.EndMonthIdx>6,INDEX(6,ThePage!User.DaysPerMonth),0)+IF(User.EndMonthIdx>7,INDEX(7,ThePage!User.DaysPerMonth),0)+IF(User.EndMonthIdx>8,INDEX(8,ThePage!User.DaysPerMonth),0)+IF(User.EndMonthIdx>9,INDEX(9,ThePage!User.DaysPerMonth),0)+IF(User.EndMonthIdx>10,INDEX(10,ThePage!User.DaysPerMonth),0) Number of days that the End Day is from the beginning of the year
DaysFromPageStart ((Prop._VisDM_Start_Year-ThePage!Prop.StartYear)*365)+(User.StartDayOfYear-ThePage!User.StartDayOfYear)+INT((Prop._VisDM_Start_Year-ThePage!Prop.StartYear)/4) Number of days that the Start Day is from the beginning date of the page
DurationDays ((Prop._VisDM_End_Year-Prop._VisDM_Start_Year)*365)+(User.EndDayOfYear-User.StartDayOfYear)+INT((Prop._VisDM_End_Year-Prop._VisDM_Start_Year)/4) Number of days between Start and End Day
StartMonthTrigger DEPENDSON(Prop._VisDM_Start_Day)+SETF(GetRef(Prop._VisDM_Start_Day.Format),”=”””&ThePage!User.MonthDays&IF(LOOKUP(User.StartMonthIdx,”8;3;5;10″)>-1,”;29;30″,IF(LOOKUP(User.StartMonthIdx,”0;2;4;6;7;9;11″)>-1,”;29;30;31″,IF(User.IsStartYearALeapYear,”;29″,””)))&””””) Forces update of the Start Day Format when the Start Month is changed
EndMonthTrigger =DEPENDSON(Prop._VisDM_End_Day)+SETF(GetRef(Prop._VisDM_End_Day.Format),”=”””&ThePage!User.MonthDays&IF(LOOKUP(User.EndMonthIdx,”8;3;5;10″)>-1,”;29;30″,IF(LOOKUP(User.EndMonthIdx,”0;2;4;6;7;9;11″)>-1,”;29;30;31″,IF(User.IsEndYearALeapYear,”;29″,””)))&””””) Forces update of the End Day Format when the End Month is changed

Shape Data

Even though I have already created the Shape Data rows, I now need to modify them, so I have shown the modified formulas in red:

Name Label Type Format Value
_VisDM_Name “Name” GUARD(0)    
_VisDM_Thematic_Area “Thematic Area” GUARD(0)
   
_VisDM_Height “Height” GUARD(0)    
_VisDM_Start_Year “Start Year” GUARD(2)    
_VisDM_Start_Month “Start Month” GUARD(1) ThePage!User.Months  
_VisDM_Start_Day “Start Day” GUARD(1) <set by User.StartMonthTrigger>  
_VisDM_End_Year “End Year” GUARD(2)    
_VisDM_End_Month “End Month” GUARD(1) ThePage!User.Months  
_VisDM_End_Day “End Day” GUARD(1) <set by User.EndMonthTrigger>  

It is necessary to GUARD() the Type formulas so that the Link Data to Shapes feature does not change them.

All of the values can be either entered manually, or they can be assigned automatically via Link Data to Shapes.

Shape Transform

Finally, I could set the formulas that control the width, height and position:

Name Formula Description
Width GUARD(User.DurationDays*ThePage!User.WidthPerDay) Ensures that the shape width is represents the Event duration
Height GUARD(Prop._VisDM_Height) Ensures that the shape height is set to the specified value
PinX GUARD(User.DaysFromPageStart*ThePage!User.WidthPerDay) Ensures that the shape PinX is represents the Event start date
LocPinX Width*0 Sets the local PinX to the left edge of the event shape
LocPinY Height*0 Sets the local PinY to the bottom edge of the event shape

I am assuming that the vertical position of the Event shape is moved manually.

So now I have a shape that looks like this:

image

However, you may have realised that an Event which starts and ends on the same day will look like this:

image

So, I need to have an alternative appearance for the case where the User.Duration Days = 0, or in other words, it is a milestone.

image

So, I added another three rows to the new Geometry section, and modified the X and Y formulas to give me a diamond with a width the same as the height:

image

I also changed the NoFill formula to FALSE for my new section, and then formulas for NoShow for the rectangle to =User.Duration=0, and for the diamond to =NOT(User.DurationDays=0)

This ensures that they are never simultaneously visible, and I get a diamond to represent a milestone, and a rectangle to represent an elapsed number of days.

image

Making the Event Master

Now, I can simply drag the Event shape to the document stencil, and rename the master as Event:

image

Linking multiple Events to Data

Now, all that is left to do is to select the Event master, then multi-select the rows in the External Data window and drag them onto the page:

image

So, the Event shapes are automatically placed horizontally, and they can be manually moved vertically. Their widths are set to the equivalent of the duration of the event, or as a milestone diamond. Their colour is optionally automatically set according to their theme, and their height has been set from the data source.

QED.

You can download the files here:

TimelinesEarlierThan1900.vsd at https://skydrive.live.com/redir.aspx?cid=3350d61bc93733a9&resid=3350D61BC93733A9!2512&parid=3350D61BC93733A9!197

TimelinesEarlierThan1900.xlsx at https://skydrive.live.com/redir.aspx?cid=3350d61bc93733a9&resid=3350D61BC93733A9!2511&parid=3350D61BC93733A9!197

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

davecra.wordpress.com/

Microsoft Office Development, and more...

PowerShell.Amsterdam

Automate, Accelerate, Accurate

johnvisiomvp

Life with Visio and other Microsoft Toys!

Title (Required)

Windows Server Essentials Tips & Tricks

Nilsandrey's Weblog

Just another WordPress.com weblog

Things that Should be Easy

Every so often (too often in the IT industry) I encounter things that should have been very easy to do but turned out to be far too complicated. My favorite topics include SharePoint, .Net development, and software architecture, especially distributed systems.

Visio Guy

Shapes, Stencils, Drawings Templates, Tutorials, Tips & Developer Info for Microsoft Visio

Hannes's Virtual Earth

Tips & Tricks around Mapping and Cloud Computing

Pluralsight blog

be smart, be clear, be visual ...

%d bloggers like this: