Date formatting...

A

ajayb

Hi,

Wonder if anyone can answer this for me as it's driving me crazy!

I have a spreadsheet I've inherited. The dates appear in cells as Nov 11,
Dec 05, Apr 08 etc. Each date is left aligned as if text. The apostrophe
doesn't appear before them when you click in the cell (i.e. not 'Nov 11).
When I go to format cells it tells me the formatting is general. There are
no macros in the workbook. However, when I enter a date in a cell it
automatically formats it as Nov-11.

Can anyone give me any ideas as to why?

TIA

A
 
H

Harald Staff

What does the formula bar show when you select one of those cells ?

HTH. Best wishes Harald
 
A

ajayb

Hi Harald,

The formula bar shows exactly what's in the cell eg 3/2003 (I apologise, in
my original posting I entered the date format incorrectly). When you click
in the formula bar and then press enter it formats the entry as a date thus:
Mar-03

Any ideas?

A
 
D

Dave Peterson

Try this against a test worksheet.
Select column A.
Format|Cells|Number Tab|choose Text

Type 3/2003 in A1
Notice that it's treated as text. You see 3/2003 in the formulabar and in the
cell.

Now select column A once again
format|cells|Number tab|choose General

Notice that A1 didn't change
But if you type 3/2003 in B1, you see Mar-03 in the cell, but 03/01/2003 (or
some variation) in the formulabar.

This experiment shows that just changing the cell's format isn't enough to
change the underlying value in the cell.

After you change the format to General, you could even just select the cell, hit
F2, then enter. Excel will know that you are reentering the value--notice that
it's a date (it looks like a date and isn't in a cell formatted as text) and
take over from there--and enter it as a date.

So if you want to enter text values, make sure you format the cell as text
first.

If you want to convert those existing values to real dates, you could do it cell
by cell (yech!)

or you could select that range
edit|Replace
what: / (slash key)
with: / (same slash key)
replace all

Excel will see that you are reentering your data and notice that it looks like a
date!
 
D

Dave Peterson

Change this sentence:

But if you type 3/2003 in B1,
to
But if you type 3/2003 in A2,

(I wanted you to type in the same column!)
 
A

ajayb

Hi Dave,

Thanks for that. I tried it as you suggested and whilst it works, it still
differs from the original spreadsheet.

When I carry out your steps then click Format-Cells-Number in a new
workbook, each cell is marked as Text, whilst in my original workbook doing
the same shows the cell as General (even in those where the entry is 3/2003)

Do you have any other ideas?

Regards

Andy
 
D

Dave Peterson

But if you enter the data while the cell is formatted as text, then it is
treated as text.

Then if you later change the format to general, those existing text values
aren't changed--even though the cell is formatted as general, the value in the
cell is still text.

You could verify that by:
format a cell (a1) as text
type 123 in that cell
put =istext(a1) in B1
You'll see True.

Format A1 as general
notice that B1 is still True.
Select A1, hit F2, then enter
and B1 will change to false.

Simply changing the cell's format doesn't change the value in the cell.
 
Top