Remove space in date

[nitsud]

Hi

Basically I am importing a list of dates from a webpage - they import like this:

28.8. 2011
10.9. 2011
18.9. 2011
24.9. 2011
01.10. 2011
15.10. 2011
23.10. 2011
29.10. 2011
05.11. 2011
19.11. 2011
26.11. 2011
03.12. 2011
10.12. 2011
17.12. 2011
21.12. 2011
26.12. 2011
31.12. 2011
02.1. 2012
14.1. 2012
21.1. 2012
31.1. 2012
04.2. 2012
11.2. 2012
25.2. 2012
03.3. 2012
10.3. 2012
17.3. 2012
24.3. 2012
31.3. 2012
07.4. 2012

I need them in this format: 2011/08/28

I have attached an example file. Don't mind if the correctly formatted dates display in a new column or sheet.

Ultimately I would then like to do this to several different "team sheets"

I would really appreciate your help!

Many thanks

[NBVC]

Welcome to the forum,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

PLEASE PM WHEN YOU HAVE DONE THIS AND I WILL DELETE THIS POST

[ZENITH]

After replacing all .'s with a space
Put this in F2 and drag down:

Please Login or Register  to view this content.

[nitsud]

Wow thank you so much!

Just one thing though - On the dates where the month is a single number such as 8 not 08 the it puts a full stop after the month. But for like the 12 month it displays perfectly. So just need to remove the full stop and add a zero!

eg.

2011/8./28


Really appreciate the help!

[martindwilson]

where is the actual page you are importing what's the url?

[nitsud]

where is the actual page you are importing what's the url?

The sample link is http://www.soccervista.com/team.php?teamid=10260

The table from that page is the Upcoming matches - which can be seen in the attachment!

Many thanks

[martindwilson]

ok when i import it from there selecting just the year to date games
it comes in with no merged fields
select col a then data/text to columns/delimited
next
next
choose date>>>> dmy >>>>click finish
it looks like this then

[nitsud]

ok when i import it from there selecting just the year to date games
it comes in with no merged fields
select col a then data/text to columns/delimited
next
next
choose date>>>> dmy >>>>click finish
it looks like this then

Thank you Martin.

It is actually for the Upcoming games table further down the page.

I did read this and try it and can't get it right.

I see you did in your attachment though. I am going to need to do this on several sheets for several teams!

Thank you so much!

I really appreciate your help!!!

Nearly there with this!

[martindwilson]

try selecting all, then format/cells/untick the merge cells box,it will probably look like its greyed out click it twice. then you can use the text to column trick on col a

[nitsud]

try selecting all, then format/cells/untick the merge cells box,it will probably look like its greyed out click it twice. then you can use the text to column trick on col a

Thank you but the merge cells check box is unchecked!

For the text to columns part -

I select the entire A column
Then text to columns
Then Delimited radio is selected, click next
Then Tab check box is checked, text qualifier " click next
Select date radio YMD click finish

Is that right? Doesn't do anything.

Thank you so much for the help!

[martindwilson]

you must be doing it wrong works fine everytime for me

[nitsud]

you must be doing it wrong works fine everytime for me

Please could you show me the screens before that and one after so I can make sure all my settings are right.

I have tried it so many times now!

Sorry about that and I really do appreciate your help!

[nitsud]

GOT IT THANK YOU MARTINDWILSON!

Really appreciate it! On that screen I was setting date to YMD instead of DMY.

Thank you so much!!!

I have just noticed though that once the data is refreshed it changes back?



Not sure if I must create another thread for this but its kind of a continuation?!

Now in another sheet I have used the =TODAY() which gives us todays date.

In a new cell I try and compare the dates we have just formatted to todays date or to tomorrows date =TODAY()+1

What I have is

The only way I could get it to work was like this

=IF(OR(H1='Man UTD'!B4,H1='Man UTD'!B5,H1='Man UTD'!B6,H1='Man UTD'!B7,H1='Man UTD'!B8,H1='Man UTD'!B9,H1='Man UTD'!B26,H1='Man UTD'!B27,H1='Man UTD'!B29),"Yes","No")

Comparing the date to the individual date cells.

This is rather hectic though when you have so many to do and need a weeks forcast.

I would have though something this would work:

=IF(H1='Man UTD'!A1:A100,"Yes","No")

But it doesn't.

Thank you again for your help - you are a champion!!!

[martindwilson]

thats 2 new problems
you could probably get someonr to write some code that would automate the process, and the second one needs to be in its own thread

[nitsud]

thats 2 new problems
you could probably get someonr to write some code that would automate the process, and the second one needs to be in its own thread

Thank you - do you know where I could get someone to do this?

[nitsud]

With the help from someone else - converting the date is easier than thought

=DateConvert(A2)

Now just check if they match

[martindwilson]

DateConvert() where did that come from? thats not a function ive heard of