Visio Date Format Pictures Affect Data Entry via the Calendar Popup

I have been creating roadmaps in Visio lately, and the client is only concerned with the months and years, so I thought it would be sensible to use a Date Format Picture to display only the month and year in the Shape Data window. What I did not expect is that the mere existence of this Date Format Picture would alter the actual date stored in the cell. In this blog, I layout my concerns about the Calendar popup that is provided for Date type Shape Data rows.

In the following example, I have created a shape, coloured pale orange below, and added 25 Date type Shape Data rows. I have not applied any format to Property1, but then I have applied each of the 17 suggested Date Format Patterns provided on a drop-down list in the Define Shape Data dialog. Finally, I added a further 7 patterns manually. I then selected Today as the value for every single Shape Data row:


When I examined the ShapeSheet for the Shape Data rows, I noticed that values for Property11 and Property12 were both storing 12th April 2012, even though Today is 26th April 2012, and the Property15, 16, 17 and 18 all store the minimum DATETIME value, which is 3oth December 1899. The extra 7 custom formats all stored a different Date value to the selected Today date.


I then inserted the value of Property1 (Prop.Row_1) 25 times, and applied the same Date Format Patterns that I had applied to each of the Shape Data rows.


So, the Date Format Picture applied to the Shape Data row does modify the data stored, when the Calendar popup is used (dates entered manually via the keyboard are not modified). But why and how? Each of the values are stored with the DATETIME(n) function, where n is the number of days since 30th Dec 1899.

Firstly, it is worth noting that the current Calendar control does not provide a Time selector, thus it can only be used to select dates …. even though the data type is DateTime!

The following is an extract from the Visio 2010 SDK for the DATTIME function:




Name Required/Optional
Data Type Description
datetime Required String Any string commonly recognized as a date and time or a reference to a cell containing a date and time.
expression Required String Any expression that yields a date and time.
lcid Optional Number Specifies the locale identifier to be used in evaluating a non-local datetime. The locale identifier is a number described in the system header files.

Return Value


If datetime is missing or cannot be interpreted as a valid date or time, DATETIME returns a #VALUE! error.

The returned value is formatted according to the short date style and time style in the system’s current Regional Settings.

The DATETIME function also accepts a single number value for expression where the integer portion of the result represents the number of days since December 30, 1899, and the decimal portion represents the fraction of a day since midnight.


I created some User-defined cells to apply some formulas, by entering the stored values in the first 4 rows, and then testing the effect of 4 significant digits after the decimal point, in the last 4 rows :


When I looked at the values, I actually had an error for the User.Row_4.Prompt until I added the INT() function.


My conclusion is that using the popup Calendar control in a Date type Shape Data row will apply the Date Format Picture before storing the value in the cell, thus modifying the value in some instances!

Unfortunately, I do not know anyway to prevent the Calendar control from being available, or replacing the default control with a better one, without creating your own custom control.

There are nicer Date and Time controls available in WPF, for example, so I use them where possible, but users can still find the Shape Data window….

For more information, see About Format Pictures (


Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.


O365 and SharePoint

Simplify Tasks

Want to learn the simple way?

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

John Goldsmith's visLog

be smart, be clear, be visual ...

Mo's blog

Personal views on Dynamics 365 for Operations and Technical Architecture.

Chris Webb's BI Blog

Microsoft Power BI, Analysis Services, MDX, DAX, M, Power Pivot and Power Query

Azure Solutions for Office 365, and more...

Rob Fahrni



Life with Visio and other Microsoft Toys!

Nilsandrey's Weblog

Just another 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

Smart graphics for visual people

%d bloggers like this: