Excel Formatting Issue

Excel Formatting Issue

Didn't find your answer?

Does any one know a quick method to resolve the issue of a spreadsheet with dates entered as text

e.g "10.12.2008"

instead of being entered in the date format. As I wish to be able to sort the spreadsheet by date.

Replies (7)

Please login or register to join the discussion.

avatar
By RichardSchollar
21st Jan 2010 15:02

.

 Hi

Two options: 

1. Ctrl+H with Find of . and Replace of / and click Replace All

2. Data>TextToColumns>Delimited>Next>and choose Import Format of Date with DMY>Finish

 

Richard

Thanks (0)
avatar
By User deleted
21st Jan 2010 15:23

Thank you

Thanks (0)
avatar
By RichardWhight
26th Jan 2010 13:08

Making Dates from Strings using Formula

Hi

So you can see how this works on a new spreadsheet do the following

Put the date 10.12.2008 in Cell A1

In Cell B1 put

=LEFT(A1,2)

In Cell C1 put

=MID(A1,4,2)

In Cell D1 put

=RIGHT(A1,4)

You can see that you now have the individual values for Day, Month & Year. You can now use this with the DATE function to return a date.

In Cell E1

=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))

If you see only a number returned then format the cell as a date and you'll get the answer you want. You can always copy and paste special with values if you want to delete the column in your sheet that has the wrong date format.

The only downfall of this is if the month part isn't always represented by two digits.

This method is especially useful when the date is really not in any recognisable format but you know where the digits are that you want (say from two or more different columns). You just need to use the appropriate string function (left, mid or right) to get the part of the string that contains date information and then pass it to the date function.

 

 

 

Thanks (0)
avatar
By User deleted
28th Jan 2010 12:51

Formating

Assuming all your dates are in the same column, highlight the column, right click go to format cells and select Date and then under type there is one that uses dd.m.yy from there you will be able to sort by date.

Thanks (0)
avatar
By RichardSchollar
28th Jan 2010 17:28

.

As stated in the original question these dates are formatted as text (and so Excel does not see them as proper numeric date values) and hence formatting as Dates or anything else for that matter will have no effect on how they sort. 

Thanks (0)
avatar
By User deleted
28th Jan 2010 20:35

Ctl H

Why not just hightlight the entire column, press Ctrl H then find . and replace with / then Replace all? Simple date format.

Thanks (0)
avatar
By User deleted
10th Feb 2010 17:22

Try text to columns

Insert a spare column to the right (to protect data from being overwritten)

Select the column

Then Data

Text to columns

Fixed width

Chose date format and the appropriate format the source data is in

Finish

This will also convert text data in American date formats.

 

 

 

 

Thanks (0)