Date format

J

Jw

hi, everyone

i am using excel xp version.

i input the following data in the sheet 1

a column
1 2003-3-17
2 2003-3-18
3 2003-2-11
4 2003-4-10
5 2003-4-11

i copied all and then past it into the sheet 2 but the year is 1999
1999-3-17
1999-3-18
1999-2-11
1999-4-10
1999-4-11

i tried to copy one cell and then past it into the sheet 2, the year is
1999.

i do not know what cause.

please help.

thanks
jimmy
 
D

David McRitchie

Hi Jimmy,
Somehow it seems you have a difference with 1900 and 1904
date systems, but I don't know how you managed to get two
different systems in the same workbook. (or are they in different
workbooks). Tools, options, calculation, ....
 
J

Jw

hi, david

thank for your email

in fact, i used daceasy version 11 accounting software to export the data in
excel 5.0 format (file a) and then i used a vlookup function to match (file
b which is manually inputted by me).

what can i do ??

thanks
jimmy



David McRitchie said:
Hi Jimmy,
Somehow it seems you have a difference with 1900 and 1904
date systems, but I don't know how you managed to get two
different systems in the same workbook. (or are they in different
workbooks). Tools, options, calculation, ....

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

hi, everyone

i am using excel xp version.

i input the following data in the sheet 1

a column
1 2003-3-17
2 2003-3-18
3 2003-2-11
4 2003-4-10
5 2003-4-11

i copied all and then past it into the sheet 2 but the year is 1999
1999-3-17
1999-3-18
1999-2-11
1999-4-10
1999-4-11

i tried to copy one cell and then past it into the sheet 2, the year is
1999.

i do not know what cause.

please help.

thanks
jimmy
Click to expand...
Click to expand...
 
D

David McRitchie

Hi Jimmy,
Depending on which side of the 1900 or 1904 date system you are
on add or subtract 365*4

If these are constants then you can...
You can do that by placing 1460 in a cell, selecting it, copy (ctrl+c),
select the column to be corrected, edit, paste, special, add or subtract

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Jw said:
hi, david

thank for your email

in fact, i used daceasy version 11 accounting software to export the data in
excel 5.0 format (file a) and then i used a vlookup function to match (file
b which is manually inputted by me).

what can i do ??

thanks
jimmy



David McRitchie said:
Hi Jimmy,
Somehow it seems you have a difference with 1900 and 1904
date systems, but I don't know how you managed to get two
different systems in the same workbook. (or are they in different
workbooks). Tools, options, calculation, ....

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

hi, everyone

i am using excel xp version.

i input the following data in the sheet 1

a column
1 2003-3-17
2 2003-3-18
3 2003-2-11
4 2003-4-10
5 2003-4-11

i copied all and then past it into the sheet 2 but the year is 1999
1999-3-17
1999-3-18
1999-2-11
1999-4-10
1999-4-11

i tried to copy one cell and then past it into the sheet 2, the year is
1999.

i do not know what cause.

please help.

thanks
jimmy
Click to expand...
Click to expand...
Click to expand...
 
D

David McRitchie

Hi Jimmy, (correction to my previous reply reposted in microsoft.public.excel)

Depending on which side of the 1900 or 1904 date system you are
on add or subtract 365*4 - 1

If these are constants then you can...
You can do that by placing 1459 in a cell, selecting it, copy (ctrl+c),
select the column to be corrected,
edit, goto, constants an number (no you've select only number constants)
edit, paste, special, add or subtract

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

=>
 
D

Dave Peterson

I think the number you want to use is 1462.

David said:
Hi Jimmy, (correction to my previous reply reposted in microsoft.public.excel)

Depending on which side of the 1900 or 1904 date system you are
on add or subtract 365*4 - 1

If these are constants then you can...
You can do that by placing 1459 in a cell, selecting it, copy (ctrl+c),
select the column to be corrected,
edit, goto, constants an number (no you've select only number constants)
edit, paste, special, add or subtract

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

=>
Click to expand...
 
D

David McRitchie

Thanks Dave you correcting my "correction" 1462 is much
better for the difference between the 1904 and 1900 Excel date systems.
don't know what I thought I was testing..
 
Top