0

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

1
  • 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
1

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.

2
  • Could you modify that to do a whole column please :) 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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