assign the formatted cell value (date) to another cell?

[Lars Uffmann]

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 T]

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]

Peter,

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

 

[Rick Rothstein \(MVP - VB\)]

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