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

  • Thread starter Thread starter Lars Uffmann
  • Start date Start date
L

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
 
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
 
Peter,

Peter said:
The "TEXT" worksheet function is pretty much the equivalent of VB's Format
function
Click to expand...

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:MM:DD 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
 
Back
Top