formatting a variable problem

[Spike]

I nave a problem formatting a variable. If i run the following in a sub it
has no effect on the variable value, howeverif i pass the variable to another
sub and run the same formatting code on it it does it as i want.

Dim datCFwd As Date

datCFwd = Sheets("Model").Range("Model_AccountingEnd").Value
datCFwd = Format(datCFwd, "dd mmm yy")

the range Sheets("Model").Range("Model_AccountingEnd") is custom formatted
as "dd-mmm-yy" but the variable actuallly holds the value as "dd/mm/yy" and
this does not change unless it is passed to another sub as explained above

Any ideas to correct this will be very gratefully received.

 

[Dave Peterson]

First, using "As Date" will keep the value a date--not text (Format() returns
text).

So either use two variables or use "As Variant"

Second, when working with dates, it's probably better to use the .value2
property.

 

[EricG]

Dates are stored internally as single precision numbers, with the whole
number part representing the date and the decimal part representing the time
of day. Your Format statement does not change that - datCFwd will always
store the date as a real number. It will not store the date as a text
string, which is what the Format statement produces. So what you're doing is
taking the value that is stored in datCFwd, converting it to a text string
using Format, and then converting it back to a real number (using the "=").
You have changed nothing.

If you want a specific text string, you should have something like:

Dim datCFwdTxt as String

datCFwdTxt = Format(datCFwd, "dd mmm yy")

This will produce a text string with the desired format for the date.

HTH,

Eric

 

[Spike]

Spot on, many thanks for that much appreciated