I have a problem formatting a variable.
if i place the following in a sub the variable value does not change; it
stays as "dd/mm/yy"

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

however if i pass this variable to another sub and run the same formatting
code the variable is changed to how i want it.

The Range("Model_AccountingEnd") is formatted as a Custom format "dd-mmm-yy"
and it does appear in this format on the relevant sheet.

if anyone has any ideas why this should be i will be very interested
--
with kind regards

Spike

You are assigning your value to a variable that is declared as a Date...
Date values do not have format... they are just dates. VB stores dates in a
Double as a whole number offset from "date zero" (which is December 30,
1899); and if there is a time value in your date, it is stored as the
fraction of a 24-hour day in the decimal portion of the Double. The
formatting of a Date variable is solely for the user's benefit and declaring
a variable as a Date just tells VB that when it displays the value to a
human, make it look like the human wants it to look. You can see this easily
enough by executing this line of code in the Immediate Window...

? Now, CDbl(Now)

--
Rick (MVP - Excel)


"Spike" wrote in message
...
I have a problem formatting a variable.
if i place the following in a sub the variable value does not change; it
stays as "dd/mm/yy"

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

however if i pass this variable to another sub and run the same formatting
code the variable is changed to how i want it.

The Range("Model_AccountingEnd") is formatted as a Custom format
"dd-mmm-yy"
and it does appear in this format on the relevant sheet.

if anyone has any ideas why this should be i will be very interested
--
with kind regards

Spike

Hi Spike,

You have declared datCFwd as date. Dates are actually stored as a numeric
value. Format function returns a string so you should dimension a string
variable for it.

Dim datCFwd as Date
Dim strDatCFwd as String

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


--
Regards,

OssieMac


"Spike" wrote:

I have a problem formatting a variable.
if i place the following in a sub the variable value does not change; it
stays as "dd/mm/yy"

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

however if i pass this variable to another sub and run the same formatting
code the variable is changed to how i want it.

The Range("Model_AccountingEnd") is formatted as a Custom format "dd-mmm-yy"
and it does appear in this format on the relevant sheet.

if anyone has any ideas why this should be i will be very interested
--
with kind regards

Spike