Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Excel 2010 UK Date to US Date


(!)

Sunnyd204's Avatar
Sunnyd204 Sunnyd204 is offline
Member with 3 posts.
THREAD STARTER
 
Join Date: Jul 2012
23-Jul-2012, 12:36 PM #1
Excel 2010 UK Date to US Date
I am frustrated with the following problem.
I need to convert the date to US formatting.


I receive a file in .csv format and the dates come as text and a normal date format all within the same column.

Example:

12/4/2012 23:16
12/4/2012 23:21
13/04/12 12:04 AM
13/04/12 12:52 AM
13/04/12 3:20 AM

The top 2, Excel reads as date. The bottom 3 excel reads as text...I need the bottom 3 to read as date.

Please HelP!!!
20_2_Many's Avatar
20_2_Many 20_2_Many is offline
Member with 504 posts.
 
Join Date: Jun 2012
23-Jul-2012, 02:13 PM #2
Are you Importing the file into excel (Data > From Text), choosing Delimited > Next > Selecting Comma as the Delimeter > Next > Changing the Column Data Format for your Date Field to Date and Selecting MDY, DMY, or YMD - whichever you Prefer > Changing any other format you may need in other columns > Finish > Chooing the Cell to beging the Data import from > OK.
?
Sunnyd204's Avatar
Sunnyd204 Sunnyd204 is offline
Member with 3 posts.
THREAD STARTER
 
Join Date: Jul 2012
23-Jul-2012, 03:49 PM #3
The file that I open is a pre-formatted csv file.
In other words, there is no comma delimiting necessary because the data is already split out in to the respective columns.

I have tried the method you suggested, and it did not work.
My guess is I need to be able to convert the UK date formatting to US. As soon as the day is 13 and up it changes it to a text cell rather than a date.
20_2_Many's Avatar
20_2_Many 20_2_Many is offline
Member with 504 posts.
 
Join Date: Jun 2012
24-Jul-2012, 05:39 AM #4
US to UK Dates
Try this.
I did not mess with the Hours except to get them out of the way.Hope your .csv does not actually come with multiple hour formats!
Run the PrepColumns. It adds 2 columns to hold the Hours and moves hours over. Then Calls the Change to UK macro.
This starts at bottom and works up to Row 2, swapping the Day and Month in each cell.
Test Reset just puts it back to the Date and Hour info from your original post.
Good Luck
Attached Files
File Type: xls USUK.xls (48.5 KB, 99 views)
etaf's Avatar
etaf   (Wayne) etaf is online now
Computer Specs
Moderator with 52,681 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
24-Jul-2012, 09:21 AM #5
i dont think its the 13 - its the AM thats the issue

its because you appear to have a time format in the csv file of 24 hour clock and 12 hour clock mixed

1st two are in 24 hour and last 3 have AM in the field - but i think 20_2_many has replied with a macro to resolve
__________________
Wayne
Please let us know what the final solution was to any problem posted
Garf13LD's Avatar
Garf13LD Garf13LD is offline
Member with 455 posts.
 
Join Date: Apr 2012
Experience: Intermediate
26-Jul-2012, 12:02 AM #6
ermm... its to US not UK
The first 2 dates reads 4 Dec 2012, which are incorrect.
The last 3 reads 4 mth(13) 2012, which are invalid.
Swapping the dd/mm to mm/dd will solve all the problem.
One way is to do a find replace in MS word.
1. open the csv file in word.
2. load up the replace dialog (Ctrl + H)
3. Click more and check use wildcards
4. in the search field use one or the following
([0-9]{2})/([0-9]{2})/(*M) for date format ##/##/##
([0-9]{1,2})/([0-9]{1,2})/(*M) for date format with 1 or 2 digits day mth
5. in the replace field use the following
\2/\1/\3
6. save the file and reopen in excel


if you need it in excel formula or vba macro, let us know.
20_2_Many's Avatar
20_2_Many 20_2_Many is offline
Member with 504 posts.
 
Join Date: Jun 2012
26-Jul-2012, 07:03 AM #7
I probably did swap the splits inadvertently.
ukdate = x(1) & "/" & x(0) & "/" & x(2)
or
ukdate = x(0) & "/" & x(1) & "/" & x(2)
will provide the mm/dd/yyyy or dd/mm/yyyy format whichever is desired.
Garf13LD's Avatar
Garf13LD Garf13LD is offline
Member with 455 posts.
 
Join Date: Apr 2012
Experience: Intermediate
26-Jul-2012, 11:17 PM #8
Excel treat date as numbers, hence after PrepColumns "split", ChangeToUK is not able to swap "real dates"

This formula I have written years back will fix it.
of cos, I will rewrite it into vba when I have time.
=VALUE(MID(TEXT(A1,"DD/MM/YY"),FIND("/",TEXT(A1,"DD/MM/YY"),1)+1,3)&LEFT(TEXT(A1,"DD/MM/YY"),FIND("/",TEXT(A1,"DD/MM/YY"),1))&RIGHT(TEXT(A1,"DD/MM/YY"),2))
Sunnyd204's Avatar
Sunnyd204 Sunnyd204 is offline
Member with 3 posts.
THREAD STARTER
 
Join Date: Jul 2012
30-Jul-2012, 09:08 AM #9
Thanks So Much Guys
I tried a few of the options suggested and it worked like a charm!

Thanks Everyone
etaf's Avatar
etaf   (Wayne) etaf is online now
Computer Specs
Moderator with 52,681 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
30-Jul-2012, 09:21 AM #10
your welcome thanks for letting us know
You can mark your own threads solved using the button at the top of the page of the thread in the upper left corner.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑