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:
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:
The application of Data Graphics automatically adds some User-defined Cells, and the Link Data to Shapes feature adds Shape Data rows:
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:
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:
I also added some User-defined Cells to assist in the computations, and enhanced the formulas in some of the Shape Data rows:
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.
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:
However, you may have realised that an Event which starts and ends on the same day will look like this:
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.
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:
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.
Making the Event Master
Now, I can simply drag the Event shape to the document stencil, and rename the master as Event:
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:
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://1drv.ms/u/s!AoRdXTjKEAo1imcEOA-14nzWEzIL
TimelinesEarlierThan1900.xlsx at https://1drv.ms/x/s!AoRdXTjKEAo1imn0jA0Kf8gbUCZx