• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

bVisual

  • Home
  • Services
    • How Visio smartness can help your business
    • Visio visual in Power BI
    • Visio Consulting Services
    • Visio Bureau Services
    • Visio Training and Support Services
  • Products
    • Visio Shape Report Converter
    • SS Plus
    • LayerManager
    • visViewer
    • Metro Icons
    • Rules Tools for Visio
    • The Visio 2010 Sessions App
    • Multi-Language Text for Visio
    • Document Imager for Visio
    • multiSelect for Visio
    • pdSelect for Visio
  • Case Studies
    • Case studies overview
    • Using Visio in Education for GIS
    • Visualizing Construction Project Schedules
    • Visio Online Business Process Mapping
    • Nexans Visio Template
    • CNEE Projects, WorldCom
    • Chase Manhattan Bank
  • News
    • Recent news
    • News archive
  • Resources
    • Articles➡
      • ShapeSheet Functions A-Z
      • Comparing Visio for the Web and Desktop
      • Customising Visio Shapes for the Web App
      • Key differences between the Visio desktop and web apps
      • Using the Visio Data Visualizer in Excel
      • Using Visio in Teams
      • Creating Visio Tabs and Apps for Teams with SharePoint Framework (SPFx)
      • Designing Power Automate Flows with Microsoft Visio
      • Innovative uses of Visio Lists
    • Webcasts ➡
      • Visio in Organizations
      • My session and other Visio sessions at MSIgnite 2019
      • Power up your Visio diagrams
      • Vision up your Visio diagrams
      • The Visio 2010 MVP Sessions
    • Visio Web Learning Resources
    • Books➡
      • Visualize Complex Processes with Microsoft Visio
      • Mastering Data Visualization with Microsoft Visio
      • Microsoft Visio Business Process Diagramming and Validation
      • Visualizing Information with Microsoft Visio
  • Blog
    • Browse blog articles
    • Visio Power BI articles
    • Visio for Web articles
    • A history of messaging and encryption
  • About us
    • About bVisual
    • Testimonials
    • Bio of David Parker
    • Contact Us
    • Website Privacy Policy
    • Website terms and conditions
    • Ariba Network
You are here: Home / Visio / Making Timelines with Dates Earlier than 1900 in Visio

Published on March 16, 2012 by David Parker

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 NameFormulaDescription
IsStartYearALeapYearMODULUS(Prop.StartYear,4)=0TRUE if Start Year is a leap year
IsEndYearALeapYearMODULUS(Prop.EndYear,4)=0TRUE 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
StartMonthIdxLOOKUP(Prop.StartMonth,Prop.StartMonth.Format)Zero-based index of Start Month
EndMonthIdxLOOKUP(Prop.EndMonth,Prop.EndMonth.Format)Zero-based index of End Month
StartMonthTriggerDEPENDSON(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
EndMonthTriggerDEPENDSON(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)
StartDayOfYearINT(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
EndDayOfYearINT(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
WidthPerDayPageWidth/User.DurationDaysWidth of page per day
msvDGCalloutGapDrawingScale/PageScale*0.0625Added 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.

NameLabelTypeFormatValue
StartYear“Start Year”2<yyyy>
StartMonth“Start Month”1User.Months<select from list>
StartDay“Start Day”1<set by User.StartMonthTrigger formula><select from list>
EndYear“End Year”2<yyyy>
EndMonth“End Month”1User.Months<select from list>
EndDay“End Day”1<set by User.EndMonthTrigger formula><select from list>
StartDate“Start Date”0GUARD(Prop.StartDay&” “&Prop.StartMonth&” “&Prop.StartYear)
EndDate“End Date”0GUARD(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

NameFormulaDescription
StartMonthIdxLOOKUP(Prop._VisDM_Start_Month,Prop._VisDM_Start_Month.Format)Zero-based index of the Start Month
EndMonthIdxLOOKUP(Prop._VisDM_End_Month,Prop._VisDM_End_Month.Format)Zero-based index of the End Month
IsStartYearALeapYearMODULUS(Prop._VisDM_Start_Year,4)=0TRUE if Start Year is a leap year
IsEndYearALeapYearMODULUS(Prop._VisDM_End_Year,4)=0TRUE if End Year is a leap year
StartDayOfYearINT(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
EndDayOfYearINT(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
StartMonthTriggerDEPENDSON(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:

NameLabelTypeFormatValue
_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:

NameFormulaDescription
WidthGUARD(User.DurationDays*ThePage!User.WidthPerDay)Ensures that the shape width is represents the Event duration
HeightGUARD(Prop._VisDM_Height)Ensures that the shape height is set to the specified value
PinXGUARD(User.DaysFromPageStart*ThePage!User.WidthPerDay)Ensures that the shape PinX is represents the Event start date
LocPinXWidth*0Sets the local PinX to the left edge of the event shape
LocPinYHeight*0Sets 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://1drv.ms/u/s!AoRdXTjKEAo1imcEOA-14nzWEzIL

TimelinesEarlierThan1900.xlsx at https://1drv.ms/x/s!AoRdXTjKEAo1imn0jA0Kf8gbUCZx

Related

Filed Under: Visio, Visio 2010 Tagged With: Timeline, Visio 2007

About David Parker

David Parker has 25 years' experience of providing data visualization solutions to companies around the globe. He is a Microsoft MVP and Visio expert.

Reader Interactions

Comments

  1. Johnson says

    August 2, 2022 at 10:49 pm

    It’s 2022 and I was searching for the same thing. The hell I’m gonna do all of this. What were they thinking when restricting TIMElines to 1900? Up to another program then. Congratulations anyway.

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

  • LinkedIn
  • Twitter

Recent Posts

  • Fixing dimensions of 2D shapes
  • Merging Linked Data from Similar Tables
  • Smart Radio Buttons and Check Boxes in Visio
  • Using Button Face Ids in Visio
  • Grid Snapping Revisited

Categories

Tags

Accessibility Add-Ins Connectors Containers Data Export Data Graphics Data Import Data Visualizer Educational Excel GraphDatabase Hyperlinks Icon Sets JavaScript LayerManager Layers Legend Link Data to Shapes Lists MSIgnite MVP Office365 Org Chart PowerApps PowerBI PowerQuery Processes Setup and Deployment Shape Data Shape Design ShapeSheet ShapeSheet Functions SharePoint 2013 SQL Teams Validation VBA Video Visio Visio 2007 Visio for the Web Visio Online Visio Services Visio Viewer Webinar

Footer

bVisual Profile

The UK-based independent Visio consultancy with a worldwide reach. We have over 25 years experience of providing data visualization solutions to companies around the globe.

Learn more about bVisual

  • Amazon
  • E-mail
  • Facebook
  • LinkedIn
  • Twitter
  • YouTube

Search this website

Recent posts

  • Fixing dimensions of 2D shapes
  • Merging Linked Data from Similar Tables
  • Smart Radio Buttons and Check Boxes in Visio
  • Using Button Face Ids in Visio
  • Grid Snapping Revisited

Copyright © 2025 · Executive Pro on Genesis Framework · WordPress · Log in