-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Convert American dates to British dates
- Thread starter Cara
- Start date
I've encountered this problem several times.
You have to pay specific attention to your data source. I've used CSV files generated through Access without date formatting being applied and ended up with a blend of US and UK dates.
So you'll get 12/25/2010 and 25/10/2010 which is easy, however, 03/03/2010 is not quite as simple to determine if you have a mix.
Check the data before hacking it apart, you could be making more of an issue for yourself by trying to correct it than not. If you are getting this data from another source, the issue most likely lies there. More often than not I find this to be the case.
However, if you've got consistent US dates then this works well for me, as Jeff has already suggested.
=DATEVALUE(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2))
You have to pay specific attention to your data source. I've used CSV files generated through Access without date formatting being applied and ended up with a blend of US and UK dates.
So you'll get 12/25/2010 and 25/10/2010 which is easy, however, 03/03/2010 is not quite as simple to determine if you have a mix.
Check the data before hacking it apart, you could be making more of an issue for yourself by trying to correct it than not. If you are getting this data from another source, the issue most likely lies there. More often than not I find this to be the case.
However, if you've got consistent US dates then this works well for me, as Jeff has already suggested.
=DATEVALUE(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2))
Last edited:
Upvote
0
Excel Facts
Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Yes.
I had a problem with a financial software while exporting data into excel some dates were correct while others were in american format.
I noticed that the american ones were aligned on the right while the UK on the left, so I developed the formula quoted above
That formula is the only one working for me, I couldn't figure out any other solution
I had a problem with a financial software while exporting data into excel some dates were correct while others were in american format.
I noticed that the american ones were aligned on the right while the UK on the left, so I developed the formula quoted above
That formula is the only one working for me, I couldn't figure out any other solution
Upvote
0
Norie
Well-known Member
- Joined
- Apr 28, 2004
- Messages
- 76,377
- Office Version
- 365
- Platform
- Windows
Phelony
This is a common problem and one of the most common solutions is to use Data>Text to columns...
Now, that doesn't work 100% of the time but it's got a pretty good hit rate.
When it doesn't work it's usually the data itself that's the problem.
Actually the best place to fix this sort of problem is perhaps when you are importing the data to Excel in the first place.
Even better you could fix the problem at source - which could be possible, depending of course where the data is coming
from, how you receive it and if you can make changes at source.
By the way, why the extra step of exporting to a CSV from Access, then importing the CSV to Excel?
Would it not be possible to do a straight import/export between Excel and Acess?
This is a common problem and one of the most common solutions is to use Data>Text to columns...
Now, that doesn't work 100% of the time but it's got a pretty good hit rate.
When it doesn't work it's usually the data itself that's the problem.
Actually the best place to fix this sort of problem is perhaps when you are importing the data to Excel in the first place.
Even better you could fix the problem at source - which could be possible, depending of course where the data is coming
from, how you receive it and if you can make changes at source.
By the way, why the extra step of exporting to a CSV from Access, then importing the CSV to Excel?
Would it not be possible to do a straight import/export between Excel and Acess?
Upvote
0
As I said above, it's 9/10 times the data itself rather than the formatting or anything Excel has done. (pretty sure I said that anyway!)
The reason for the extra step is that we get .csv files with huge amounts of data from our finance system that our puny 2003 (don't blame me!) can't handle, so I have to import and wash it through Access because it doesn't have data row limitations and to run a few queries to clean the data and do some basic calculations. Data is then drawn from the exported .csv into a pivot where it is then branched out to the required areas of various spreadsheets.
It was the only way I could resolve having 400k+ rows of data and make Excel 2003 make sense of it without having to split it into multiple worksheets and do an assembly job.
I send a request every week to IT for Office 2007, long and very annoying story...
With regard to this particular issue, if it's on a localised scale and the data is within Excel, I will generally convert to text and then rearrange it or jam in a formula - whichever takes my mood really, other than that I will revert to the formatting controls in my Access queries where the problem can usually be sorted out.
The reason for the extra step is that we get .csv files with huge amounts of data from our finance system that our puny 2003 (don't blame me!) can't handle, so I have to import and wash it through Access because it doesn't have data row limitations and to run a few queries to clean the data and do some basic calculations. Data is then drawn from the exported .csv into a pivot where it is then branched out to the required areas of various spreadsheets.
It was the only way I could resolve having 400k+ rows of data and make Excel 2003 make sense of it without having to split it into multiple worksheets and do an assembly job.
I send a request every week to IT for Office 2007, long and very annoying story...
With regard to this particular issue, if it's on a localised scale and the data is within Excel, I will generally convert to text and then rearrange it or jam in a formula - whichever takes my mood really, other than that I will revert to the formatting controls in my Access queries where the problem can usually be sorted out.
Last edited:
Upvote
0
"Is it possible to use a formula or cell formatting in Excel to convert a column of American dates mm/dd/yyyy to British formats dd/mm/yyyyy.
I have tried the conventional custom formatting but it doesn't modify the existing dates"
Am I missing something? You can simply format the date in a cell or use a simple macro to format any number of selected cells at once.
Sub USDates()
Selection.NumberFormat = "mm/dd/yyyy"
End Sub
Sub BritDates()
Selection.NumberFormat = "dd/mm/yyyy"
End Sub
These work fine on my UK computer.
I have tried the conventional custom formatting but it doesn't modify the existing dates"
Am I missing something? You can simply format the date in a cell or use a simple macro to format any number of selected cells at once.
Sub USDates()
Selection.NumberFormat = "mm/dd/yyyy"
End Sub
Sub BritDates()
Selection.NumberFormat = "dd/mm/yyyy"
End Sub
These work fine on my UK computer.
Upvote
0
Norie
Well-known Member
- Joined
- Apr 28, 2004
- Messages
- 76,377
- Office Version
- 365
- Platform
- Windows
Phelony
So how are you getting the data into Excel in the end?
If you are initially dealing with them in Access have you tried sorting the date problem there?
Or if you are already doing that, how are you doing it?
Sorry for the questions, I'm just wondering what's happening with the dates that's causing the problem.
Might be something outwith your control, just curious though.
So how are you getting the data into Excel in the end?
If you are initially dealing with them in Access have you tried sorting the date problem there?
Or if you are already doing that, how are you doing it?
Sorry for the questions, I'm just wondering what's happening with the dates that's causing the problem.
Might be something outwith your control, just curious though.
Upvote
0
No worries, it's as under control as I can make it without rebuilding the finance database but if I've missed something I'm happy to have another look at what I do. 
The issue in access is a lack of formatting control. If the query runs without having a defined date format it doesn't automatically select the UK system. For some reason it thinks it's funny to put every day below the 12th as the month and then pass on the issue.
So I'll get 03/12/2011 (MM/DD/YYYY) followed by 13/03/2011 (DD/MM/YYYY), it reverts to UK dating when the US dating no longer makes sense to it.
I've simply put the control into the query and the problem is resolved.
As we get .csv data in the first instance it obviously doesn't have formatting, so the problem is entirely user generated. But occasionally with new data the error recreates itself and needs to be fixed again.
Date formatting is not something that's built into our finance system for some absurd reason as we have US offices, so it reports as data was input. (Which is another problem unto itself!)
The issue in access is a lack of formatting control. If the query runs without having a defined date format it doesn't automatically select the UK system. For some reason it thinks it's funny to put every day below the 12th as the month and then pass on the issue.
So I'll get 03/12/2011 (MM/DD/YYYY) followed by 13/03/2011 (DD/MM/YYYY), it reverts to UK dating when the US dating no longer makes sense to it.
I've simply put the control into the query and the problem is resolved.
As we get .csv data in the first instance it obviously doesn't have formatting, so the problem is entirely user generated. But occasionally with new data the error recreates itself and needs to be fixed again.
Date formatting is not something that's built into our finance system for some absurd reason as we have US offices, so it reports as data was input. (Which is another problem unto itself!)
Upvote
0
Similar threads
- Solved
