assign formatted cell value to another cell

[Lars Uffmann]

Hello everyone!

If A1 has a floating point number for a date, e.g.
39647.25 for 2008-07-18 06:00:00
and I want to have that formatted string in a new cell - does anyone
know a formula to apply to another column, so it puts the *formatted*
value there, instead of the original number? I want the string
"2008-07-18 06:00:00"
as the value of my new cell, and I can't find a way to do that with a
Range formula at the moment (I want to avoid looping over each row and
doing it manually in VBA, for speed reasons).

Thanks in advance!

Lars

 

[Dave Peterson]

=TEXT(A1,"yyyy-mm-dd hh:mm:ss")

Is how I'd do it with my USA functions and USA formatting characters.

 

[Pete_UK]

Generally, a formula cannot return a format, only a value.

However, you might like to try this:

=TEXT(Sheet1!A1,"yyyy-mm-dd hh:mm:ss")

to get it to look as you want it (it is a text value, however).

Hope this helps.

Pete

 

[Lars Uffmann]

Dave, and Pete_UK also

=TEXT(A1,"yyyy-mm-dd hh:mm:ss")
Is how I'd do it with my USA functions and USA formatting characters.

Thanks to both of you, the text function is what gets the job done -
only downside being that MS deliberately created an incompatibility
there for systems with different regional settings, for it will only
take the localized format string. The irony is - I am running a 100%
english XP installation, just for date and currency displays, I use the
german denotation - and due to that, Excel demands that my format string
be using the german "J" (Jahr) for year, and "T" for Tag (day) - while
it does accept the "h" for hour... Way to go, MS!


Best Regards,

Lars

 

[Dave Peterson]

International issues are a pain!

Glad you have a solution, er, something that works for you.