About Date, Time, and Duration Values
You can perform operations in formulas using date, time, and duration values. In Microsoft Visio, a date and time expression can be evaluated as a single value. A date and time expression is any expression commonly recognized as a date and/or time or a reference to a cell containing a date and/or time. This includes strings and numbers that look like a date and time, and date and time values returned from functions.
Date and time values in Visio are stored internally as a 64-bit floating point number. The value to the left of the decimal represents the number of days since December 30, 1899. The value to the right of the decimal represents the fraction of a day since midnight. Noon is represented by .5.
To use dates and times within an expression (rather than as a single constant), you must use the appropriate function to identify them as date and time values.
Valid dates
| Valid dates | Valid dates | Valid dates |
|---|---|---|
| "2/28" |
"2/28/99" |
"2/28/1999" |
| "2-28" |
"2-28-99" |
"2-28/1999" |
| "6 Mar 99" |
"6 Mar" |
"6 Mar 99" |
| "1 January 99" |
"Jan 1, 99" |
"Jan 1, 1999" |
| "Jan 00" |
"January, 2000" |
"Jan 1, 00" |
Valid times
| Valid times | Valid times | Valid times |
|---|---|---|
| "3:45" |
"3:45:27" |
"7a" |
| "7 am" |
"7 p" |
"7:30 PM" |
Date and time functions
| Function | Description |
|---|---|
| DATE |
Converts numbers to a date value. |
| DATETIME |
Converts a string to a date and time value. |
| DATEVALUE |
Converts a string to a date value. |
| NOW |
Returns the current system date as a date and time value. |
| TIME |
Converts numbers to a time value. |
| TIMEVALUE |
Converts a string to a time value. |
| DAY |
Returns the day component in a date and time expression. |
| DAYOFYEAR |
Returns the sequential day of the year in a date and time expression. |
| HOUR |
Returns the hours component in a date and time expression. |
| MINUTE |
Returns the minutes component in a date and time expression. |
| MONTH |
Returns the month component in a date and time expression. |
| SECOND |
Returns the seconds component in a date and time expression. |
| WEEKDAY |
Returns the number of the weekday in a date and time expression. |
| YEAR |
Returns the year component in a date and time expression. |
Duration
You can perform operations that calculate duration or elapsed time. Duration is stored internally as days and the fraction of a day. For example, 1 elapsed week, 7 elapsed days, and 168 elapsed hours all are stored internally as 7.0, but are displayed with the appropriate units.
Visio recognizes the units of duration in the following table.
| Unit | Abbreviation | Universal abbreviation |
|---|---|---|
| elapsed day |
eday, ed. | ed |
| elapsed hour |
ehour, eh. | eh |
| elapsed minute |
eminute, em. | em |
| elapsed second |
esecond, es. | es |
| elapsed week |
eweek, ew. | ew |
You can add a date and time to a duration to calculate a new date and time. You can perform the operations listed in this table using dates, times, and durations.
| Input | Result |
|---|---|
| Date-time +/- duration |
Date and time value |
| Duration +/- date-time |
Date and time value |
| Duration +/- duration |
Duration value |
| Date-time + date-time |
Date and time value |
| Date-time - date-time |
Duration value |