Calling Excel programmers

[Steve Maskery]

Morning all. Anyone familiar with Excel functions?

I have a cell with a text string in it. The text string looks like this:

Jan 16, 2010 2:40 PM GMT

That's all in one cell. Excel doesn't recognise it as a date. I want to convert it to the date Jan 16 2010 so that I can plot figures against dates and I don't know how to do it. Can anyone help? I don't even know what to search for. A free virtual pint is on offer

Many thanks
Steve

 

[mickthetree]

Steve

You need to format the cell as a date cell. Depending on your version, right click on the cell and select format cell.

Choose the date option then select the format you want (or closest to).

You can then perform functions against it.

 

[RogerS]

Not convinced that's right.

To perform functions on a date, don't you need to use =date() type of thing?

Steve, your text string is quite complicated. Why the GMT for example? Is this string from somewhere else? A series of strings? Or did you happen to choose it as an example? If a series of strings then I guess a rather complex string extraction and then conversion to date is needed.

But better way to start is =date()

Roger

 

[mickthetree]

I'm assuming the date has been imported in that format.

Actually, Excel doesnt recognise that to be able to convert it to a dtae,

you will have to break it apart first.

Try this

in Cell B1 put =LEFT(A1,12)

In Cell C1 =LEFT(B1,6)

In Cell D1 =RIGHT(B1,4)

In cell E1 =C1&" "&D1

That will give you some options to play with. Choose which one you want to use and set that cell (or column) as a date formattted cell as I said before.

You can then perform functions on it.

Now wheres this virtual pub???

 

[mickthetree]

assuming hte text string is in A1 of course

 

[Tony Spear]

Actually Steve it's perfectly simple:

Select the cell or first cell in the column you want to format.

On the toolbar select "Format"
On the drop down menu select "Cells" or "Column"
This brings up the "format cells" window.
Select "date"
This brings up a sub-menu where you can select the format that you want to use to display the date.

Then in the cell you enter the date as dd/mm/yr in your case 16/01/2010
and Bob 's your wotsit! :wink:

If you're entering non-consecutive dates you need to enter them individually, on the other hand for consecutive dates you select the bottom right hand corner of the cell you have formatted and drag it down.
This automatically adds one day to the previous cell.

I've just brought up a worksheet and dunnit so I know it works! :wink:

 

[Jake]

If it is an email time and date, you should be able to specify the time and date format in your email programme. /wild guess

 

[mickthetree]

Again assuming the date string is in A1 you could use

=MID(A1,9,4)&" "&LEFT(A1,3)&" "&MID(A1,5,2)

 

[Tony Spear]

Sorry Steve, slight mistake on my part:

You can't use the "Format Column" function to do the date format. You need to select a range of cells in order to format it. However if you do the drag down as I suggested you'll automatically copy the format down, so if you format a blank cell and drag it down you'll only copy the formatting and then you can enter whatever date you need.

Sorry for any confusion.

 

[mickthetree]

Right!

here you go steve

Cell A1, put your date string

Cell B1 copy and paste this =MID(A1,5,2)&"/"&LEFT(A1,3)&"/"&MID(A1,9,4)

Cell C1 put this =DATEVALUE(B1)

Then format cell C1 to whatever date format you want.

DONE!

 

[Steve Maskery]

Thank you guys.

Yes the string is imported and I'd already tried formatting the column as a date, which it doesn't recognize.

All that LEN stuff rings very distant bells. I'll have a play along those lines. I'll also have to see what happens when it is Jan 1 instead of Jan 12, for example- that will alter the length of the required clip, won't it?

Thanks all.
S

 

[Tony Spear]

Steve:

It's so long since I've used Excel seriously that I keep remembering things.

You can of course select an entire column in order to format all the cells in it.

Assuming that you want to put the dates in column A, just click on the letter A above column A. This then selects the entire column.

Then just use format/cells/date etc as I said earlier and this formats every cell in the column.

You can then enter dates individually, or if you want consecutive dates just drag any cell down and it automatically adds one day to the cell you are dragging.

I've just tried it and it works fine.

 

[RogerS]

The thing is, Tony, that your formatting will only work on a number. I pasted Steve's string into Excel and then went through the format/cells/date. Excel doesn't complain but then again neither does it do anything because the contents of the cell are a string.

Mick's approach is the correct one as it parses the string and then converts what's there into something that the date functions can recognise.

I think :wink:

 

[Steve Maskery]

Tony
What version of Excel are you using, because that does not work on mine. I expected it to, because that's what I did originally, but it doesn't change the string at all on my machine (Mac, XL 2008).
S

 

[Steve Maskery]

Not just me then!
I think the LEN stuff will sort me out.
Thanks
Steve

 

[mickthetree]

if the output for the 1st is 01 instead of just 1 then the conversion will still work ok.

 

[Steve Maskery]

Sadly, it's not. I really need to extract from the beginning to the comma, for the day and month, and then the year after the comma.
S

 

[mickthetree]

Hey steve

Is it not possible to format how the date is output in the first place, from whatever it is being output from?

Is it something you copy and paste in? linked to a database?

 

[Steve Maskery]

No, I have no control over the format, it's from Google Checkout.

I can get the format out correctly, but only one month at a time, and I have to do that manually, whereas I can cut an entire page of transactions and paste it as HTML and the job is done, 3 years in 3 pages instead of 36 separate C&P operations.

Cheers
Steve

 

[Jake]

You can get rid of some of it crudely by highlighting the column and using find/replace all (comma for nothing, pm gmt for nothing, am gmt for nothing etc). Still leaves you with the time though!