Converting a date in Excel & sorting hyperlinks

VoucherMan

I've got a text file and all the dates, which I want to use are in US format.

All the lines are in the format -

June 28, 2013 - rest of text

so it's easy enough to separate the date. I've used =LEFT(A1,FIND("-",A1)-2) but I'm always open to better suggestions any anyone knows a better way.

I can't find a simple way to get Excel to recognise the date, other than changing my regional settings!

I've managed to find a couple of ways to do it, the shortest being
=REPLACE(REPLACE(A1,FIND(",",A1),1,MID(A1,1,FIND(" ",A1,2)-1)),1,FIND(" ",A1,2),"")+0 which, I think, replaces the comma with the month, then removes the month from the start of the date.

Is there a simpler way to convert US to UK dates, or to get Excel to recognise them?

[Deleted User]

Hi,

did you try, format > cells > date.

VoucherMan

[Deleted User] wrote:

Hi,

did you try, format > cells > date.

It's not recognised as a date or value so formatting doesn't help.

stevemcol

Voucher man. I've played around with it but I can't come up with an easy way of converting those text strings into dates. Just variations on what you've come up with. There are some specialist excel forums you might want to try.

Richie(UK)

Hi Voucherman,

See if this helps (assumes that the data is in column A starting at A1):

1. Select column A (click the A at the top of the column).

2. Select 'Text to Columns' from the Data menu. Choose the 'Delimited' option and click 'Next'. Select the options for 'Comma', 'Space' and 'Other' - add a hyphen in the 'Other' box. Click 'Finish'.

3. In a clean column (G being the first on my test workbook) enter this in cell G1 : =(B1&" "&A1&" "&C1)*1. Copy the formula down as required.

4. Select the G column (click on the G at the top of the column) and select 'Cells' from the Format menu. Select 'Date' from the 'Category' menu and then your chosen Date format.

5. That's all folks! :-)

VoucherMan

stevemcol wrote: »

There are some specialist excel forums you might want to try.

That's where I found this one.

http://www.excelforum.com/excel-general/667958-convert-a-text-date-into-a-proper-date.html

I modified it a bit as mine doesn't have a timestamp. Still much shorter than the other solution I came across.

Just gets a bit long when I combine the date separation formula as I don't want a separate column for it.

=IF(A1="","",REPLACE(REPLACE(LEFT(A1,FIND("-",A1)-2),FIND(",",LEFT(A1,FIND("-",A1)-2)),1,MID(LEFT(A1,FIND("-",A1)-2),1,FIND(" ",LEFT(A1,FIND("-",A1)-2),2)-1)),1,FIND(" ",LEFT(A1,FIND("-",A1)-2),2),"")+0)

It work fine just looks 'clumsy' - if you can use that term for a formula. Knowing how the folks on here are sometimes open to a challenge I thought I'd give it a try.


Thanks Richie as well.

It works - although not with the *1 at the end. I'd have to repeat it whenever I had more data though and I prefer something I can just paste into column A and pick out the results at the other end.
I'll keep it in mind though. I'd heard of it before but never knew what it did and I can think of a few places it would have been useful.

stevemcol

Awesome. For complex formulas like that it's sometimes neat to create your own functions. Google 'create my own functions in excel'

VoucherMan

I'm just lazy though

I like to get others to do the work for me.

Like when I wanted to display an email address on a sheet rather than the person's name.
Fortunately Google found a good solution for me though.

For all the complex, to me at least, functions built into Excel I'm often surprised at some of the relatively simple ones it's missing. Maybe it's just me but I'd have thought being able to display [email]john_smith@my_email.com[/email] could be more useful than [EMAIL="john_smith@my_email.com"]john smith[/EMAIL]

Mirno

It doesn't like "Month Day, Year".
It does like "Day Month, Year" though.
Then use datevalue to turn it into a date...

=DATEVALUE(MID(A1,FIND(" ", A1)+1, FIND(",", A1)-FIND(" ", A1))&" "&LEFT(A1,FIND(" ", A1)-1)&MID(A1,FIND(",",A1),6))

- without the datevalue and broken down by the concatenations:
MID(A1,FIND(" ", A1)+1, FIND(",", A1)-FIND(" ", A1))
LEFT(A1,FIND(" ", A1)-1)
MID(A1,FIND(",",A1),6))

So the day, - can be simplified if you know the dates are always two characters (01 for the first, rather than 1).
Then the month
Then ", year" - leaving the comma on is easier, and unless you plan on using the spreadsheet in the year 10000 (or going back to 999 or lower), we can simplify this to be always 6 characters (comma, space, year).

Should for the case as you described...

Mirno

Ainsley1

There are many ways to skin a cat (so the saying goes) and with Excel it is so versatile that it is very true here too. There is nothing really wrong with the solutions shown already to work but as you say not very neat and not all that easy to reproduce without errors except when copying and pasting in cells or to understand/follow as there are no explanatory notes within the formulas.

If you are looking for neatness the best way is to create the formula in VBA in your spreadsheet and then just reference this in the cell where you wish the result.

The cell entry would just look something like "=Dvconv(A1)" i.e. like a built in function, where the cell containing the data is 'A1' and the VBA function created is 'Dvconv' ( a sensible name for the function is best selected that is self explanatory!) so very simplistic to see in Excel cells and very neat.

Within the function code (that is normally hidden and can be password protected [not unbreakable!]) can be explanatory comments to explicitly tell the reader what each step in the function achieves. It can easily be further protected if you have copyright issues!!!

If you need help writing the VBA code PM me.

VoucherMan

Mirno wrote: »

=DATEVALUE(MID(A1,FIND(" ", A1)+1, FIND(",", A1)-FIND(" ", A1))&" "&LEFT(A1,FIND(" ", A1)-1)&MID(A1,FIND(",",A1),6))

Ainsley1 wrote: »

If you are looking for neatness the best way is to create the formula in VBA in your spreadsheet and then just reference this in the cell where you wish the result.

Thanks for both. I have used UDF's occasionally but not often, mainly down to lack of knowledge. The original formula is working fine so don't really want to change it now.

Besides which I've moved on to my next little project which currently has me stumped.

Just in case anyone's bored or feels like a challenge



I've a list of names (A4: D6) in this example which are hyperlinked to G1:P1 .
In the full spreadsheet the names (G1:P1) will be on separate sheets, the idea being to click on the name in A4: D6 to get to the page.

I want to have all the names displayed together on the page rather than scrolling down a list so spread it over a few columns.

The only (simple) way I've come up with so far to sort the names alphabetically is to have an extra column (F) so cell F11 =G1 and so on. And A4 =F11. Then when I sort column F my A4: D6 list will be in order. Unfortunately doing it like this the hyperlinks stay linked to the original target so now clicking on B5 (EEE) will take me to K5 (FFF)


If this makes sense any anyone can knows a method to sort the list while keeping the correct likes i'll welcome suggestions.

should point out the list is not fixed. Names will get added to it which is why I want to be able to re-sort it.

I'm sure Google could find the answer if only I could work out the correct question to ask but so far I've failed!