Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting a variable problem
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. -- with kind regards Spike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting a variable problem
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. Spike wrote: 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. -- with kind regards Spike -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting a variable problem
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" wrote: 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. -- with kind regards Spike |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting a variable problem
Spot on, many thanks for that much appreciated
-- with kind regards Spike "EricG" wrote: 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" wrote: 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. -- with kind regards Spike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting using variable text | Excel Discussion (Misc queries) | |||
formatting a variable | Excel Programming | |||
Set formatting variable | Excel Programming | |||
Formatting a Variable | Excel Programming | |||
Problem trying to us a range variable as an array variable | Excel Programming |