Format A Date Expression

You can custom format an expression using a specific date or time with the FormatDateTime function. Doing so returns a Variant data type value with the specified formatting. See Chapter 3 for more information on data types.

The FormatDateTime function uses two different arguments, of which only the first argument is required: Date, and NamedFormat. The Date argument identifies the date expression that you want to format and accepts cell references, variable references, string expressions, or numeric values. If you reference a cell, the cell must have default formatting. In other words, the cell must have Default, and not Date, or some other formatting value, when you view the formatting.

You can reference a cell using any of the cell range reference options discussed in Chapter 11. For example, if the date you want to format is located in cell A1 you can type the following code using the Cells property to reference that cell: FormatDateTime(Cells(1,1)).

The NamedFormat argument specifies the formatting of the expression. You can use one of the predefined formatting constants. If you omit the NamedFormat argument the FormatDateTime function uses the vbGeneralDate constant as the default value. The vbGeneral constant instructs Excel to format the date expression with the system date settings and formats the time portion with the system long time settings.

Windows maintains your default date and time settings on the Regional Options dialog box, which you can access through the Start menu. When you use a constant with the NamedFormat argument, you specify the combination of these settings that you want for formatting your date and time values. By changing the values on this dialog box, you affect how the dates and times display when you use the FormatDateTime function.

FORMAT A DATE EXPRESSION

FORMAT A DATE EXPRESSION

'-n Create a new subroutine.

B Type the Dim statement to define variables for the subroutine.

< Type DateVar = DateExp, replacing DateVar with the variable to hold the date expression specified by DateExp.

'-n Create a new subroutine.

B Type the Dim statement to define variables for the subroutine.

< Type DateVar = DateExp, replacing DateVar with the variable to hold the date expression specified by DateExp.

■ The example assigns date values from the corresponding worksheet.

jsxxe:

CONSTANT

VALUE

DESCRIPTION

vbGeneralDate

0

Default value if NamedFormat argument is omitted. Displays the date using the system short date format and the time using the system long time format.

vbLongDate

1

Displays the date using the system long date format.

vbShortDate

2

Displays the date using the system short date format.

vbLongTime

3

Displays the time using the system time format.

vbShortTime

4

Displays the time using a 24hour clock format commonly referred to as Military time. For example, 6:00 p.m. formats as 18:00.

^Q Type DateVar2 = FormatDateTime(DateVar, vbformat), replacing DateVar2 with the result and vbformat with the constant that indicates desired format.

1 File Edit View Insert Format Tools Data Window Help Type a question for help t _ d? X

X ii-^l "! Anal i io . b i u m m m >:

B1 - fr GC

/1999 3:45:00 PM B

A

C D

E

F

G

H

1

6/2/1999 15:45| 6/2/1999

2 3

8/10/2000 11:23 Thursday, August 10, 2000

7/11/2001 17:45 7/11/2001

4

4/23/1999 9:45 9:45:00 AM

10

20 21 22

26 27

H

Rea

> H|\ 5heet6 / Sheet 1 / Sheet2 \ Sheets / Sheet4 / Sheet3 /

M 1 H

V

Q Type the remaining VBA code.

□ Switch to Excel and run the associated macro.

■ The macro runs and reformats each of the specified date expressions.

0 0

Post a comment