Hi everyone, simple question and I'm hoping there's a simple answer:

I have a date column A (double value), and I need it formatted in column
B for output to a text file. I'd like to do the equivalent of

Range("B:B").Formula = "Format(RC1, ""YYYY-DD-MM\Thh:mm:ss"")"

Sadly, Excel will not have the Format function in a cell. It's kinda
ridiculous that I fail to do what I want, because setting the columns
Range("A:A").NumberFormat = "YYYY-DD-MM\Thh:mm:ss"
does exactly what I want, except that it does not store the formatted
string in the cells value.

Is there any way to assign this formatted value as the .value to another
cell? Or does someone know a workaround?

Thanks in advance!

Lars

The "TEXT" worksheet function is pretty much the equivalent of VB's Format
function

=TEXT(A1,"YYYY-DD-MM\Thh:mm:ss")

or with code
range("a1") = Now
Range("b2").Formula = "=TEXT(A1,""YYYY-DD-MM\Thh:mm:ss"")"

Regards,
Peter T

"Lars Uffmann" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi everyone, simple question and I'm hoping there's a simple answer:
>
> I have a date column A (double value), and I need it formatted in column B
> for output to a text file. I'd like to do the equivalent of
>
> Range("B:B").Formula = "Format(RC1, ""YYYY-DD-MM\Thh:mm:ss"")"
>
> Sadly, Excel will not have the Format function in a cell. It's kinda
> ridiculous that I fail to do what I want, because setting the columns
> Range("A:A").NumberFormat = "YYYY-DD-MM\Thh:mm:ss"
> does exactly what I want, except that it does not store the formatted
> string in the cells value.
>
> Is there any way to assign this formatted value as the .value to another
> cell? Or does someone know a workaround?
>
> Thanks in advance!
>
> Lars

Peter,

Peter T wrote:
> The "TEXT" worksheet function is pretty much the equivalent of VB's Format
> function

Thank you very much, that is exactly what I wanted to know Though I
do kind of wonder why I have to know 2 different function names for
doing the same thing in VBA or cell formulas...

The only issue here is - while assigning "YYYY:MMD hh:mm:ss" to the
NumberFormat works perfecly, the format string passed to the text
function in the cells formula does not convert the format string to the
computer's region settings, and thus if you have german regional
settings (and the irony is - I made a point of setting my system up with
an english XP!) - it will only work if you use "JJJJ-MM-DD" etc. for the
format string. Go figure...

Thanks a lot, case closed I guess

Best Regards,

Lars

The two functions are not the same... they have some functional overlaps
between them (the usage you asked about being one of them), but there are
many differences between them as well. The two functions exist in different
worlds and, as such, have functionalities built in which cater to the worlds
they exist in.

Rick


"Lars Uffmann" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Peter,
>
> Peter T wrote:
>> The "TEXT" worksheet function is pretty much the equivalent of VB's
>> Format function
>
> Thank you very much, that is exactly what I wanted to know Though I do
> kind of wonder why I have to know 2 different function names for doing the
> same thing in VBA or cell formulas...
>
> The only issue here is - while assigning "YYYY:MMD hh:mm:ss" to the
> NumberFormat works perfecly, the format string passed to the text function
> in the cells formula does not convert the format string to the computer's
> region settings, and thus if you have german regional settings (and the
> irony is - I made a point of setting my system up with an english XP!) -
> it will only work if you use "JJJJ-MM-DD" etc. for the format string. Go
> figure...
>
> Thanks a lot, case closed I guess
>
> Best Regards,
>
> Lars