Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with Date format
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with Date format
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 On Mon, 17 Aug 2009 15:51:01 -0700, Lise wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with Date format
"Lise" wrote:
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 ----- "Lise" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date format problem | Excel Discussion (Misc queries) | |||
Date Format Problem | Excel Worksheet Functions | |||
Date Format Problem | Excel Discussion (Misc queries) | |||
Date format problem | Excel Worksheet Functions | |||
Date Format problem | Excel Discussion (Misc queries) |