How can I get the 'as-displayed' value

[jay]

Hi,

I am trying to obtain the "as-displayed" value of a cell (which has a custom format), to be appended to a filename in a 'Saveas' macro.

Issue:
Cell "A5" has the custom format of; mmddyyyyhhmm

My formula in cell "A5" is: =now()

the result of the above which is 'displayed' in cell A5 is: 031020070841

Problem:
Whenever I attempt to extract the "as-displayed" data of cell "A5", I always get the dates serial number as in: 3618335648 or some other form such as 36159.23455 etc.

Using cell "A10" as a repository for the "as-displayed" data, therein I've tried the formulas of =right(A5,12) and a couple of others, but still not the required result of: 031020070841

What would you suggest that I try ?

Thanks

Jay in Calif.

[Leith Ross]

Hello Jay,

To capture the "as displayed" cell value, use the Text function. Here is an example...

A5 formula: =TEXT(Now(), "mmddyyyyhhmm")

Now when you reference A5's value you will get the formatted text, and not the internal Date-Time numeric value.

Sincerely,
Leith Ross

[VBA Noob]

Hi,

How about

=VALUE(TEXT(A1,"mmddyyyyhhmm"))

or just =A5 then paste special values

VBA Noob

[jay]

The formula provided by Leith provides the desired result :-)

The formula provide by VBA Noob produces an exponential result. (assuming A5 initially contained the formula =now(), and the second suggestion of 'paste special' produces the date/time format in decimal, like: 35189.34566

Thanks to both of you for your responses, both of which helps us all to learn.

Jay