Solved

format unformatted date entries

Posted on 2014-04-07
6
329 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
6 Comments
 
LVL 8

Expert Comment

by:itjockey
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 50

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

791 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