convert Google Analytics date format to excel

**nsixsixsix** · 10-15-2010, 10:24 PM

I need to convert Google analytics date values to proper excel dates

the data is in the following format:

Friday, September 24, 2010

any easy way to do this?

edit: using office 2010

**hardeep.kanwar** · 10-16-2010, 01:31 AM

Originally Posted by nsixsixsix

I need to convert Google analytics date values to proper excel dates

the data is in the following format:

Friday, September 24, 2010

any easy way to do this?

edit: using office 2010

=TEXT(A1,"DDDD,MMMM dd,yyyy")

**DonkeyOte** · 10-16-2010, 05:24 AM

Based on UK locale - with the Date String in A1 you can use the below to generate the Date Value:

B1:
=0+SUBSTITUTE(RIGHT(A1,8),",",REPLACE(LEFT(A1,LEN(A1)-8),1,FIND(",",A1),""))
format as Date

**daddylonglegs** · 10-16-2010, 06:39 AM

Originally Posted by DonkeyOte

=0+SUBSTITUTE(RIGHT(A1,8),",",REPLACE(LEFT(A1,LEN(A1)-8),1,FIND(",",A1),""))

Hello DonkeyOte, not sure that would always work, depends whether single digit days are shown with leading zeroes or not. It would fail on a date like

Saturday, October 9, 2010

My suggestion would be

=TRIM(SUBSTITUTE(RIGHT(A1,8),",",MID(A1,FIND(",",A1)+1,4)))+0

**DonkeyOte** · 10-16-2010, 06:46 AM

Yes I should have added an outer TRIM prior to coercion - thanks.

=0+TRIM(SUBSTITUTE(RIGHT(A1,8),",",REPLACE(LEFT(A1,LEN(A1)-8),1,FIND(",",A1),"")))

**teylyn** · 10-16-2010, 05:54 AM

nsixsixsix,

edit: using office 2010

You just signed up today. Why did you specify your Excel version as 2003 if you are indeed using 2010? The version info is there for a reason. Please edit your profile and provide relevant information.