I need to reference a date cell in another sheet in my workbook. I select
the cell that needs to display the referenced content. Then in the formula
bar I type = . Then I move to the sheet containing the cell I want to
reference. I select this cell and then click the green checkmark next to the
formula bar. The desired date value now shows up in my display cell in the
correct format. But I need to add text to this cell. Now my display date
loses its format and becomes a plane number.

Here is my reference =Sheet1!B15

Here is what I want to do = "(" & Sheet1!B15 & ")"

I also tried surrounding the reference with $

=Sheet1!$B$15

This did not help. As soon as I add text I get this:
(39752)

Is there a way to retain the date format for what I want to do? How to do
this?

Thanks,
Rich

Hi Rich,

Just change the format between the double quotes to whatever format you
desire.

="("&TEXT(Sheet1!B15,"dd/mm/yyyy"&")")


--
Regards,

OssieMac


"Rich" wrote:

I need to reference a date cell in another sheet in my workbook. I select
the cell that needs to display the referenced content. Then in the formula
bar I type = . Then I move to the sheet containing the cell I want to
reference. I select this cell and then click the green checkmark next to the
formula bar. The desired date value now shows up in my display cell in the
correct format. But I need to add text to this cell. Now my display date
loses its format and becomes a plane number.

Here is my reference =Sheet1!B15

Here is what I want to do = "(" & Sheet1!B15 & ")"

I also tried surrounding the reference with $

=Sheet1!$B$15

This did not help. As soon as I add text I get this:
(39752)

Is there a way to retain the date format for what I want to do? How to do
this?

Thanks,
Rich

Perfect. Thanks.

"OssieMac" wrote:

Hi Rich,

Just change the format between the double quotes to whatever format you
desire.

="("&TEXT(Sheet1!B15,"dd/mm/yyyy"&")")


--
Regards,

OssieMac


"Rich" wrote:

I need to reference a date cell in another sheet in my workbook. I select
the cell that needs to display the referenced content. Then in the formula
bar I type = . Then I move to the sheet containing the cell I want to
reference. I select this cell and then click the green checkmark next to the
formula bar. The desired date value now shows up in my display cell in the
correct format. But I need to add text to this cell. Now my display date
loses its format and becomes a plane number.

Here is my reference =Sheet1!B15

Here is what I want to do = "(" & Sheet1!B15 & ")"

I also tried surrounding the reference with $

=Sheet1!$B$15

This did not help. As soon as I add text I get this:
(39752)

Is there a way to retain the date format for what I want to do? How to do
this?

Thanks,
Rich

I should have added that you can also use the number format and simply create
your own custom format and that way the cell remains as a date instead of
text.

Use this custom format (dd/mm/yyyy)

Your formula would then be =Sheet1!B15

--
Regards,

OssieMac