Worksheet function for cell's displayed value

Not per se - if you know the format you can ape it via the TEXT function, eg:

Please Login or Register  to view this content.

Alternatives would as you say require UDF or use of XLM GET.CELL calls via Defined Names (type_num 53)

Hi,

I don't understand your question. If A1 contains =TODAY(() it will as you say return 12/01/2011 and have the underlying value 40878.

If in B1 you enter =A1, you'll still get the text 12/01/2011 in B1

There is a TEXT() function but as I say I don't understand your request. Can you clarify, if necessary upload and example workbook which shows the result you expect.

Rgds

Thanks for your replies. I ended up using the text() function with a date format string but as it's so easy to get the displayed text via VBA i thought you should be able to do the same using a worksheet function in a macro-disabled workbook without having to recreate the date format.

Richard, the reason for my question was that i had a column of dates and a column of other text which I needed to concatenate together into a new column. Its true that if (following your example) you enter =A1 in B1, you get the formatted date. However if you want to concatenate anything else with that date string, the date serial comes out instead. Eg put '="The date is " & A1' in B1 and you'll get "The date is 40878" rather than "The date is 12/01/2011"

Hi,
OK understood.
In that case use

Please Login or Register  to view this content.

Rgds