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.

Improve this question
3
  • 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 Commented Jun 26, 2016 at 20:01
  • Has been asked many times before and there are many answers out there. Just pick the one you want: stackoverflow.com/questions/7691485/… OR superuser.com/questions/307496/… (just to reference the ones which are most likely to be appropriate for you). Commented Jun 26, 2016 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 Super User 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.) Commented Jun 26, 2016 at 20:46

1 Answer 1

Reset to default
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)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.