Excell 2007 Dates and combining 2 cells

[Robin Scott]

I have created a date in Excel with the format of 03/01/01 in the form of
mm/dd/yy

Sometimes it will display the date in the cell and sometimes it will
display a 5 digit number.
Also, even if it displays correctly in a cell when combining 2 cells (1
cell being the word date- and the other cell being the date as 03/01/01in
the form of mm/dd/yy The combination will be the date and a six digit number.
Example Date-16425.
What do I need to do in these situations to that it will display the date
correctly in one cell( 03/03/01) and display the date correctly in combined
cells (Date- 03/03/01)

Sincerely,

Robin

 

[Dave Peterson]

If you're using a formula to concatenate/combined two cells, you can use
something like:

=a1&text(a2,"mm/dd/yy")

If all the dates appear as those plain old numbers, you may be looking at
formulas:
Tools|Options|view tab|uncheck formulas
(xl2003 menus)

The shortcut key for this is:
ctrl-`
(control backquote -- to the left of the 1/! on my USA keyboard)

 

[Gord Dibben]

The 5-digit number is how Excel stores dates as serail numbers.

Formatting will display as mm/dd/yy

Combining turns the cell to text.

Try =A1 & TEXT(B1,"mm/dd/yy")

assuming string "Date-" in A1 and date in B1


Gord Dibben MS Excel MVP


On Thu, 16 Oct 2008 14:53:01 -0700, Robin Scott <Robin

 

[ShaneDevenshire]

Hi,

Excel store dates as whole numbers starting with 1 which represents January
1, 1900 and going through 12/31/9999 which is 2,958,465. The time is stored
as the decimal portion of a number. So 0.5 represents Noon, half way through
the day. This way a single number is used to store both the date and the
time. When you see a five digit number which you think should be a date, you
should suspect that it isn't formatted as a date - click Format, Cells,
Number tab and note the format that is selected, it won't be a date. The
reason everyone is telling you that a five digit number is a date is because
date ranging from 5/18/1927 to 10/13/2173 will be five digit numbers.

 

[Robin Scott]

If you're using a formula to concatenate/combined two cells, you can use
something like:

=a1&text(a2,"mm/dd/yy")

If all the dates appear as those plain old numbers, you may be looking at
formulas:
Tools|Options|view tab|uncheck formulas
(xl2003 menus)

The shortcut key for this is:
ctrl-`
(control backquote -- to the left of the 1/! on my USA keyboard)

Thank you for your help. It fixed the problem.

 

[Robin Scott]

Thank you for your help. It answered my question, and fixed the problem.

 

[Robin Scott]

Thank you for giving insight as to why the date was displayed as a number.