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:

image

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.

image

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.

image

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:

Syntax

DATETIME(“datetime“|expression[,lcid])

Parameters

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
Datetime

Remarks

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 :

image

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

image

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 (http://office.microsoft.com/en-us/visio-help/HV080400010.aspx)

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: