Solved

format unformatted date entries

Posted on 2014-04-07
6
338 Views
Last Modified: 2014-04-16
The attached spreadsheet displays a list of over 15,000 date entries in column 'A'. These dates are not correctly formatted as dates. Do you know how I would go about doing this so I can then sort via date?
date-formatting.xlsx
0
Comment
Question by:rob700
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39983460
Select A1 & Home Tab - SortSort
0
 

Author Comment

by:rob700
ID: 39983472
Please refer to the attached document. This is my point the dates currently are not currently in the correct formate e.g. 01_feb

Sorting this way will not order them by date.

I need to change all of them so they are correctly formatted e.g 01/02/2013.

Then I will be able to sort them correctly via date.

Thanks
Rob
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 39983505
Hi,

If all the dates year are the same

you could use a helper's column with this formula

=DATEVALUE(SUBSTITUTE(A2,"_"," ")&" 2014")

Open in new window

Regards
date-formattingV1.xlsx
0
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39983520
Since it seems you want 2013 dates?

Add a helper column with formula:

=EDATE(SUBSTITUTE(A2,"_","/")+0,-12)

copied down.

Then you can copy and paste special > Values over original if desired and delete the helper.

Then sort normally.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39983895
Witout a helper column

- Change the computer date to 2013
- Do a search/replace and change all underscores ( _ ) to blank spaces
- Change the computer date back.

This will change all entries to dates in 2013 only.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question