Excel month and date reverse
Lina100
Posted messages
10
Status
Membre
-
Evie -
Evie -
Hello,
I extracted data from Datastream into Excel 2007, for the first 15 days of the month, the days and months are reversed in Excel (for example, 01/03/2007 for January 3, 2007).
The last 15 days of the month are normal (for example, 15/01/1987).
I am working on
Windows Vista.
I calculate the days between two dates but for example, for January 3, 2007, Excel recognizes 01/03/07, so March 1!!!
What can I do?
Thank you
I extracted data from Datastream into Excel 2007, for the first 15 days of the month, the days and months are reversed in Excel (for example, 01/03/2007 for January 3, 2007).
The last 15 days of the month are normal (for example, 15/01/1987).
I am working on
Windows Vista.
I calculate the days between two dates but for example, for January 3, 2007, Excel recognizes 01/03/07, so March 1!!!
What can I do?
Thank you
Configuration: Windows Vista Internet Explorer 7.0
20 réponses
Our previous messages overlapped.
Try the following procedure: select the cells with dates in American format => Data => Convert => DMY instead of MDY
@+
EDIT : the procedure from gbinforme that I hadn't seen before posting is certainly more practical.
--
He who asks a question is a fool for five minutes, but he who does not ask remains a fool for life.
(Chinese proverb)
Try the following procedure: select the cells with dates in American format => Data => Convert => DMY instead of MDY
@+
EDIT : the procedure from gbinforme that I hadn't seen before posting is certainly more practical.
--
He who asks a question is a fool for five minutes, but he who does not ask remains a fool for life.
(Chinese proverb)
Ara
Thank you, it works perfectly even for the first 15 days!
Hello,
Open your Excel sheet and select all (Ctrl+A), then right-click => Format Cells => Date => under "Regional Settings" check French.
Regards
--
He who asks a question is a fool for five minutes; he who never asks is a fool for life.
(Chinese Proverb)
Open your Excel sheet and select all (Ctrl+A), then right-click => Format Cells => Date => under "Regional Settings" check French.
Regards
--
He who asks a question is a fool for five minutes; he who never asks is a fool for life.
(Chinese Proverb)
Hello,
When your date, for example June 10, 2015, is written in the cell as 10/06/2015, Excel interprets the info as an American date (MM/DD/YYYY) and then pastes it as is.
In short:
You need to give the Excel sheet the American version:
activecell.value = Format(MyDate, "MM/DD/YYYY")
Excel on its side, once received, will convert it back to French automatically, and you will then have a JJ/MM/YYYY format.
Am I clear?
When your date, for example June 10, 2015, is written in the cell as 10/06/2015, Excel interprets the info as an American date (MM/DD/YYYY) and then pastes it as is.
In short:
You need to give the Excel sheet the American version:
activecell.value = Format(MyDate, "MM/DD/YYYY")
Excel on its side, once received, will convert it back to French automatically, and you will then have a JJ/MM/YYYY format.
Am I clear?
Hello,
it's already done but it doesn't change anything because for the first 15 days, the month and the day are inverted for all the years.
it's already done but it doesn't change anything because for the first 15 days, the month and the day are inverted for all the years.
"I extracted data from datastream on Excel 2007,for the first 15 days of the month"--
He who asks a question is foolish for five minutes, he who does not dare is foolish for his whole life.
(Chinese proverb)
I extracted it on Windows 2007, and now I'm working on Vista. The file was saved in Excel "2003-2007".
So why are only the first data of the month reversed and not the last ones which are normal?
So why are only the first data of the month reversed and not the last ones which are normal?
In my opinion, the explanation lies in the fact that the extracted data only concerned the first 15 days of the month and that the dates in the datastream are in US "English" format.
--
He who asks a question is foolish for five minutes; he who does not dare is foolish for life.
(Chinese proverb)
--
He who asks a question is foolish for five minutes; he who does not dare is foolish for life.
(Chinese proverb)
Sorry, I misinterpreted your first post.
I will keep looking.
If I have any news, I'll let you know.
@+
--
He who asks a question is a fool for five minutes; he who does not ask is a fool for a lifetime.
(Chinese proverb)
I will keep looking.
If I have any news, I'll let you know.
@+
--
He who asks a question is a fool for five minutes; he who does not ask is a fool for a lifetime.
(Chinese proverb)
The problem indeed comes from the fact that the dates are in English (American), I have 2 groups of dates, 1 group that goes from 1 to 12 (aligned right) and the other group from 13 to 31 (aligned left). I think it's because the month goes up to 12. So, I set the "cell format"/regional settings to "English." When I calculate the number of days between the 2 groups, the result is incorrect (negative instead of positive).
Hello
This is a well-known Excel issue, and in fact, only the first 12 days are reversed because beyond that, the date 1/13/2008 would not be plausible.
You should try to re-import the file by forcing the cell format to DD/MM/YYYY.
--
Always Zen
This is a well-known Excel issue, and in fact, only the first 12 days are reversed because beyond that, the date 1/13/2008 would not be plausible.
You should try to re-import the file by forcing the cell format to DD/MM/YYYY.
--
Always Zen
I tried in English and then in French, but the result is
01/12/2007 and the other 13/01/2007 or the inverse.
The calculation between 2 dates between 2 groups is wrong.
See you later!
01/12/2007 and the other 13/01/2007 or the inverse.
The calculation between 2 dates between 2 groups is wrong.
See you later!
Dates beginning with 01/06/94, for example, have a cell format of "date/ *14/2001 and French regional settings."
Dates beginning with 13/01/07 have a "standard" format.
@+
Dates beginning with 13/01/07 have a "standard" format.
@+
Hello
That's the problem, at the time of import you need to enforce the format to jma because afterwards it gets more complicated even though it's possible.
--
Always zen
That's the problem, at the time of import you need to enforce the format to jma because afterwards it gets more complicated even though it's possible.
--
Always zen
I will extract the data, but I don't think he will ask me for the date format. I see the dates correctly on datastream and then I extract.
The problem is that it's urgent; I need to work on this data.
Thank you.
The problem is that it's urgent; I need to work on this data.
Thank you.
Hello
You need to extract them in txt format if you can, and then Excel will ask for conversion upon opening, and there you can force the format.
--
Always zen
You need to extract them in txt format if you can, and then Excel will ask for conversion upon opening, and there you can force the format.
--
Always zen
I extracted yesterday and I managed to do it. So it’s simple, I extracted today and I don’t remember how to do it but I did something simple.
Poor Google, mindlessly returning posts from 6 years ago in the top results, I allow myself to respond to this post even though it’s 6 years old...
I think I know what’s going on:
when creating the datastream, it seems natural to have an SQL result in the French format:
then in VBA we populate a cell with this result:
But Excel must consider the default value as 'MM/DD/YYYY' (and when that’s not possible it defaults to DD/MM/YYYY).
The serialization is wrong, and now there’s nothing to be done...
Solution:
Excel successfully performs the "serialization" of the date without error. Then choose the format you want in cell formatting.
On my side, the problem has disappeared.
I think I know what’s going on:
when creating the datastream, it seems natural to have an SQL result in the French format:
select to_char(maDate,'DD/MM/YYYY') from maTable
then in VBA we populate a cell with this result:
ThisWorkbook.Sheets(1).Cells(x, 1).Value = CStr(oRs.Fields("maDate").Value & "") But Excel must consider the default value as 'MM/DD/YYYY' (and when that’s not possible it defaults to DD/MM/YYYY).
The serialization is wrong, and now there’s nothing to be done...
Solution:
select to_char(maDate,'MM/DD/YYYY') from maTable
Excel successfully performs the "serialization" of the date without error. Then choose the format you want in cell formatting.
On my side, the problem has disappeared.