Allotments 4 All
News:
Picture posting is enabled for all :)
Home
Forum
Help
Search
Calendar
Gallery
Chat
Login
Register
Allotments 4 All
»
General
»
Computers / Internet
(Moderator:
Admin aka Dan
) »
Topic:
Any Excel wizards
« previous
next »
Print
Pages: [
1
]
Author
Topic: Any Excel wizards (Read 2560 times)
keef
Hectare
Posts: 861
Any Excel wizards
«
on:
April 29, 2008, 16:36:32 »
I have column of data which an unformated time / date, example 02:50:00 Mon 11/05 (ie hh:mm:ss ddd mm/dd). I need to covert this into dd/mm (or 05/11 for my example) - any ideas.
Logged
Straight outt'a compton - West Berkshire.
Please excuse my spelling, i am an engineer
dtw
Hectare
Posts: 1,186
What grows, You decide!
Re: Any Excel wizards
«
Reply #1 on:
April 29, 2008, 17:33:29 »
highlight the column,
click format, cells,
click on date,
then select the format you want in the next box.
Logged
keef
Hectare
Posts: 861
Re: Any Excel wizards
«
Reply #2 on:
April 29, 2008, 18:01:57 »
Not that simple i'm afraid - excel does'nt reckognise it as a date to start with, so it does nothing....
Logged
Straight outt'a compton - West Berkshire.
Please excuse my spelling, i am an engineer
BAK
Wiki Editor
Hectare
Posts: 529
Re: Any Excel wizards
«
Reply #3 on:
April 29, 2008, 18:07:53 »
what happens if you set up another column and use the text function, eg ...
=text(cell,"dd/mm")?
Logged
http://www.bkthisandthat.org.uk
Tatiana
Quarter Acre
Posts: 52
Re: Any Excel wizards
«
Reply #4 on:
April 29, 2008, 18:15:57 »
If you just want to extract the mm/yy and are not too bothered whether the cell is formatted, you could use
=RIGHT(CELL NUMBER,5)
for example for cell B4
=RIGHT(B4,5)
This extracts the last 5 characters from the cell and gives you month and year only.
Any help?
Logged
jennym
Hectare
Posts: 3,329
Essex/Suffolk border
Re: Any Excel wizards
«
Reply #5 on:
April 29, 2008, 18:17:09 »
You could possibly use Edit, Find, and Replace, but you may have to change your criteria if you have many different dates. There are options within the Replace function to insert a date format.
Logged
Rhubarb Thrasher
Hectare
Posts: 2,713
Dark Side Of The Rhubarb
Re: Any Excel wizards
«
Reply #6 on:
April 29, 2008, 18:27:48 »
if the date figures all have the same no of digits, you could record a little Macro to do it, or maybe easier copy the column into Word, and do the Macro there,and paste the new column back. Heath Robinson, but i'm rubbish with Excel
Logged
Pesky Wabbit
Hectare
Posts: 881
Where's my(palm oil free)KRAFT choclit Easter Egg?
Re: Any Excel wizards
«
Reply #7 on:
April 29, 2008, 18:36:01 »
It depends if this is a one-off or needs to be a excel function.
If it were a one-off, I'd ...
Paste the column of text into Word as text;
Select ALL the text;
Convert text to table using the space, " ", as the separater in the 'Other' box;
Delete the unwanted columns ie Time and Day, leaving just the Date column (mm/dd).
Select the whole table (ie the one column left);
Convert table to text using paragraph marks as the separater;
Ensure the whole data is still selected;
Convert text to table using "/" as the separater in the 'Other' box;
This should give two columns, one for 'mm', the other for 'dd'.
Cut and paste a whole column so that they are in the order required;
Select the whole table (ie both columns);
Convert table to text using "/" as the separater in the 'Other' box;
The data is now in text in "dd/mm" format;
Try pasting this back into excel and selecting the required cell format.
«
Last Edit: April 29, 2008, 18:39:38 by Pesky Wabbit
»
Logged
keef
Hectare
Posts: 861
Re: Any Excel wizards
«
Reply #8 on:
April 29, 2008, 18:48:05 »
Thanks for all the tips, however i figured out a way (probably much more complicated than needed). I imported the data as a space delimted file, so i got colums
A B C
HH:MM:SS MMM mm/dd
or
A B C
02:50:00 Mon 11/05
The import wizard actually recognised the recognised column C as a date, so i could then swap it back to UK format (dd/mm)... Job done.
Anyway, saved me hacking around 4 files each with 32000 records...
Logged
Straight outt'a compton - West Berkshire.
Please excuse my spelling, i am an engineer
Larkshall
Hectare
Posts: 806
Near Cambourne (Cambs.)
Re: Any Excel wizards
«
Reply #9 on:
May 23, 2008, 22:38:41 »
I don't have this problem with Open Office v2.4, you can configure a column as Date and the parameters are all available to enter.
Logged
Organiser, Mid Anglia Computer Users (Est. 1988)
Member of the Cambridge Cyclists Touring Club
Print
Pages: [
1
]
« previous
next »
Allotments 4 All
»
General
»
Computers / Internet
(Moderator:
Admin aka Dan
) »
Topic:
Any Excel wizards
anything
SimplePortal 2.3.5 © 2008-2012, SimplePortal