Problem with Date format

[Lise]

Hi

I have two worksheets in one I am asking it to copy what is in the other on
a specific cell. The first worksheet has dates and blank cells when the
second sheet copies it changes the date format to a strange numbering and
where it should be blank makes up a date - I have also tried requestein if
blanl levae blank otherwise copy and still no good

Example
Sheet one Sheet two
01/05/09 4568
0/01/1900
15/07/09 3576

The two formulas used in sheet two have been
='05th August Issues'!M2

and

=IF('05th August Issues'!M2="","",M2)

Thanks

Lise

 

[Gord Dibben]

Depending upon your Windows Regional and Language Settings, Excel could be
interpreting those dates as year 1909 and not 2009 which would give you
those strange 4-digit numbers.

If Excel was interpreting 09 as 2009 you would get 5-digit numbers when
cells are formatted to General.

01/05/2009 would be 39818 or 39934 depending upon short date settings.

Format the cells on destination sheet as Date and see what date you get.

The 0/01/1900 is the date a blank cell would return when formatted as Date.


Gord Dibben MS Excel MVP

 

[JoeU2004]

The two formulas used in sheet two have been
='05th August Issues'!M2
and
=IF('05th August Issues'!M2="","",M2)

You need to use the second form in order to "copy" cells that appear blank.

And you need to explicitly format this cell in "sheet two" to the date
format that you want.


----- original message -----