0

I have a csv file which im opening in Excel. When I try to convert the date column (which is previously General). I'm getting wrong Dates.

Below Images will elaborate the same: Before Editing the column data type

If I select the column and change its data type to date , it is giving me wrong dates. After Editing the column datatype

The dates should be in the range of 1973, but its showing 1905. How do I rectify it.

Ravi
  • 659
  • 2
  • 10
  • 32
  • Open the CSV in excel without any modification and right click on 1 of the date cells and click format cells, on the number tab click "custom" and let me know what its set to in the type box – Steven Martin Jun 26 '16 at 20:01
  • Has been asked many times before and there are many answers out there. Just pick the one you want: http://stackoverflow.com/questions/7691485/how-to-globally-stop-excel-date-autoformatting OR http://superuser.com/questions/307496/how-can-i-set-excel-to-always-import-all-columns-of-csv-files-as-text (just to reference the ones which are most likely to be appropriate for you). – Ralph Jun 26 '16 at 20:06
  • I'm voting to close this question as off-topic because it is not a programming question. It may be more suitable for [su] instead. (Although the answer there will be the same; you're expecting something that is absolutely not a date to be converted to a date. A date is not a range of years separated by a dot. Excel is converting that non-date to the proper value for the floating point number 1973.1667, which is one thousand, nine hundred and seventy three days after Dec 30, 1899 at midnight, which is the zero (base) date in Excel.) – Ken White Jun 26 '16 at 20:46

1 Answers1

0

The problem is different: what do you expect from 1973.1667 to be?

I suggest that this is YYYY.yyyy while yyyy is partial year and excel simply will not accept it that way. Excel dates are like DDDD.ddddd. Also: changing it to "Date" doesn't change the value itself, it is just the way to be displayed what changes.

You could calculate the date like this (for B2):

=DATE(INT(B2),ROUND(MOD(B2,1)*12+1,0),1)
Dirk Reichel
  • 7,989
  • 1
  • 15
  • 31