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:
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 :
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….