About date, time, and duration values

Microsoft Office ShapeSheet

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.

Click to show or hide information.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"

Click to show or hide information.Valid times

"3:45"

"3:45:27"

"7a"

"7 am"

"7 p"

"7:30 PM"

Click to show or hide information.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.

Click to show or hide information.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.

The Visio application 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