Excel Date Format Problem - conversion wanted

  Covergirl 09:56 18 Aug 2010
Locked

Hopefully someone can provide a quick & easy solution for me

I'm getting a report from a web based app which outputs a table of login and logout dates and times into an Excel spreadsheet.

The dates are in US format with no leading zeros and have a text delimiter e.g.
'8/18/2010 9:05:47 AM
'6/9/2010 11:51:07 AM
'11/26/2009 2:06:38 PM

(By the way, the date is set as English in the Preferences and XP & Excel are setup as UK English)

I have to provide a "Monthly Usage Chart" so I really need to convert this date to UK format (e.g. 07/12/2010) and then convert the date to a Month and Year which I can then chart up.

I can do various conversions to date value, and split up the date then concatenate it back into UK format but it's messy and inconsistent due to the 'no leading zero' issue.

Anybody any idea of a function or a piece of code to output the date in UK format or output Month and Year please?


Thanks in advance

  VoG II 10:39 18 Aug 2010

In a spare column

=MID(A1,2,FIND(" ",A1)-1)

and copy down. Then copy those results then Edit > Paste Special, tick Values and click OK.

Then select those results, Date > Text to Columns, click Next twice, tick Date and select MDY then click Finish.

You can then use MONTH to get the months.

  Covergirl 12:43 18 Aug 2010

. . .it's a start !

Excel 2003 SP3. Forgot to mention that.


Firstly your formula returns the following:

Original Output Result
8/18/2010 8:31:37 AM /18/2010 or
11/9/2009 2:05:53 PM 1/9/2009 or
10/30/2009 12:55:55 PM 0/30/2009
and so on

Secondly, the month is the first one or two characters of the string, so this formula is returning 18 as the month in the first example above.

Thirdly, the Text to Columns needs to use Delimited with Other selected as a slash to separate the data.

=================================================

Alternative method would be to use a FIND for "/" and a FIND for " ", then a LEFT for the first value -1 and a MID for the second value to get the year.

=FIND("/",A2)
=LEFT(A2,D2-1)
=FIND(" ",A2)
=MID(A2,F2-4,4)
=CONCATENATE(E2,"/",G2)
which returns
2 8 10 2010 8/2010

This is not really a problem, but I'm using several columns to do two FINDS, a LEFT and a MID.

On top of that, =MONTH does not work - all I ever get back is blxxdy JANUARY! ! How does this work then?

Actually, the reason I posted here was in order to find some other way rather than FIND, LEFT, RIGHT, MID, and MONTH.

Anybody any ideas ? The date string does not seem to be responding to any "Function" manipulation

  VoG II 12:57 18 Aug 2010

I don't think that you've understood what I intended.

I think you'll need to use this formula to extract the American date as a string

=LEFT(A1,FIND(" ",A1)-1)

Then copy and paste over as values.

Then Data > Text to columns. Don't select any delimiter, on the third screen tick Date and select MDY then click Finish. That will ,convert them to British dates.

If you want to extract the month name from a date just use

=TEXT(A1,"mmmmm")

or to get the month number

=MONTH(A1)

  Covergirl 13:28 18 Aug 2010

The revised formula extracts the date as a text string e.g.
8/18/2010
11/30/2009
12/1/2009
i.e. just removes the timestamp.

Text to Columns does nothing except to dates that 'appear' to be UK format already - from the above sample that is the 12/1/2009 value only.
The default settings are Delimited with Tab set as delimiter, so clicking Next twice takes me to the third screen and clicking DMY, then Finish does nothing except to 12/1/2009 on which I can select Cells>Format>Number to get 39825.

Sorry, I can't work out what's going wrong here

  Covergirl 13:31 18 Aug 2010

=TEXT(A1,"mmmmm") doesn't appear to work on mine unless I use it on UK format date and time and change the mmmmmm to Capitals MMMMMMM

  VoG II 13:47 18 Aug 2010

Text to Columns worked for me with your sample data.

If you click my yellow envelope to send me a PM I'll let you have an e-mail address to send me the file (if it doesn't contain sensitive data). That will have to be later today.

  KremmenUK 15:01 18 Aug 2010

I experimented with a macro:

Put any of your values in Cell A1 then place this into a macro and run it:

Range("B1").Select
ActiveCell.Value = Format(Range("A1"), "dd/mm/yy hh:mm")

It will convert US to UK and you just need to play with the Format statement

  Covergirl 15:27 18 Aug 2010

VoG - I will consider your offer thanks, but it will be tomorrow before I can send

  Covergirl 08:32 19 Aug 2010

As it stands, your little macro does the job converting US to UK dates with just one little thing I can live with.

Where the US date could be seen as either UK or US, i.e. both date and month figures are 12 or under, it turns the US date into a UK date e.g. 12/04/2010 - great; otherwise the converted version ends up as a text value e.g.'13/04/2010 which will not Format as a number.

I don't need to format as a number so no problem.

VoG - your =text(A1,"MMMMMMM YYYY") converts the UK date to Month and Year fine. After a Copy, Paste Special, Values this field is fully pivotable and therefore creates a good simple bar chart. It also appears to be working in lower case now, as per your original post.

Thanks all

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

See the work of famous artists playing with toys

iPad Pro 10.5in (2017) review

Comment faire une capture d’écran sur un Mac ?