Allotments 4 All

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.

highlight the column,
click format, cells,
click on date,
then select the format you want in the next box.

Not that simple i'm afraid - excel does'nt reckognise it as a date to start with, so it does nothing....

what happens if you set up another column and use the text function, eg ...

=text(cell,"dd/mm")?

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?

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.

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

 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.

 

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...

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.