Date Wont Display As Date

  • Thread starter Thread starter Minitman
  • Start date Start date
M

Minitman

Greetings,

This is a simple one, I hope.

Here is the problem formula:

="Service Date"&CHAR(10)&Enter!AI4

I am trying to get this formula to show as:

Service Date
01/15/04

Instead I get:

Service Date
38001

Any ideas on how to get the date to show and not the date code?

TIA

-Steve Moulton
 
Hi John, you can't format a cell that contains a concatenated string in that
way. You have to use the TEXT function to format the numeric data, as in Peo's
example.
 
Hi Minitman!

Try:

="Service Date"&CHAR(10)&TEXT(Enter!A14,"dd-mmm-yyyy")

The cell needs to be formatted to receive wrapped text. Adjust the
format string in the text function to taste.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Thanks Norman,

Both you and Peo came up with the same solution. I guess great minds
think alike. ;^>

-Minitman
 
Hi Minitman!

Now you've upset Peo with that comparison! <vbg>

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
I am having a problem with the date in a spreadsheet. I enter the date as
8/20/62 and have cell format set to 20-Aug-62. I have two sheets open and am
toggling with Ctrl+Tab, which usually works well, but I occasionally hit the
key above the Tab. When I do, all columns open to header width, hiddens
columns unhide and the date column data converts to five digit numbers, i.e.
22878. The Function bar contents still read 8/20/62. Usually by the time I
realize what has happened, it is too late for Ctrl+Z to correct the problem.
I realize I am probably unintentionally hitting a hot key combo, and would
like to learn how to undefine it. Any suggestions would be appreciated.

I am using Excel 2002, XP Pro and it is networked.

Thanks,

Ray
--
Delete the _REMOVE_ before replying.

Greetings,

This is a simple one, I hope.

Here is the problem formula:

="Service Date"&CHAR(10)&Enter!AI4

I am trying to get this formula to show as:

Service Date
01/15/04

Instead I get:

Service Date
38001

Any ideas on how to get the date to show and not the date code?

TIA

-Steve Moulton
 
Ray

CRTL + `(above the TAB key) toggles "View Formulas" on/off.

Just hit it again to revert. Your data is not being changed.

No need to hit CRTL + Z.

Gord Dibben Excel MVP
 
Back
Top