losing date format when referencing date cell on another sheet and
 

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

losing date format when referencing date cell on another sheet and
 

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

losing date format when referencing date cell on another sheet
 

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

losing date format when referencing date cell on another sheet
 

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