Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am collating my server access logs to get a cumulative count of HTTP requests made to the server over a period of a few months.

Access is logged as a date time stamp like so:

[02/Apr/2013:13:41:39 +0100]

How can I sort this is MS Excel 2010?

The problem is the sorting is only doing the numbers at the start of the date and ignoring the month segment.

Anyway to sort this?


share|improve this question
The difficulty here is converting your current text string to a number that Excel can recognize as a date. I think the cleanest way would be to have Access do the conversion prior to export, consider an ISO compliant format like yyyy-mm-dd. – dav May 14 '13 at 14:36
up vote 1 down vote accepted

IF time is not critical to your calculation, you can use this formula to convert the text to a date:

Assuming your value is in A1, then

=DATE(MID(A1,9,4),MONTH("1 "&(MID(A1,5,3))),MID(A1,2,2))

This will return an Excel aware 04/02/13 (based on regional date settings.

share|improve this answer
Could you modify that to do a whole column please :) – kaleeway May 14 '13 at 14:55
Thanks, all you need to do is fill down and the relative references (e.g. A1) will automatically update for you. – dav May 14 '13 at 16:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .