Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

Thanks

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
add comment

1 Answer

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 :) –  loosebruce 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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.