Sorting Records by Times

[homeguard]

ok so im not sure how to explain this but ill give it my best shot.

I have an excel sheet that has a list of order info. heres a sample of what it looks like:

701 44:37.0 Starting order split <34010993;01> gregory_pm 34010993
702 45:16.0 Script complete for <34010993;0106> Rx <24073318> gregory_pm 34010993
701 03:37.0 Starting order split <34010995;01> knox_br 34010995
702 04:26.0 Script complete for <34010995;0101> Rx <24239630> knox_br 34010995
701 26:49.0 Starting order split <34011015;01> flander_ar 34011015
702 32:09.0 Script complete for <34011015;0101> Rx <24008174> flander_ar 34011015
701 34:01.0 Starting order split <34011015;01> flander_ar 34011015
701 27:08.0 Starting order split <34011061;01> flander_ar 34011061
702 27:26.0 Script complete for <34011061;0105> Rx <24240139> flander_ar 34011061
702 27:55.0 Script complete for <34011061;0103> Rx <24240083> flander_ar 34011061
701 52:00.0 Starting order split <34011173;01> parker_tp 34011173
702 52:46.0 Script complete for <34011173;0101> Rx <24071140> parker_tp 34011173
701 03:35.0 Starting order split <34011369;01> sexton_pa 34011369
702 04:00.0 Script complete for <34011369;0101> Rx <24240569> sexton_pa 34011369
701 49:02.0 Starting order split <34011668;01> knox_br 34011668
702 50:59.0 Script complete for <34011668;0104> Rx <21441348> knox_br 34011668
701 34:20.0 Starting order split <34011764;01> hicks_jo 34011764
702 34:44.0 Script complete for <34011764;0102> Rx <22787965> hicks_jo 34011764
702 35:06.0 Script complete for <34011764;0101> Rx <22787933> hicks_jo 34011764
702 36:11.0 Script complete for <34011764;0103> Rx <22788283> hicks_jo 34011764
701 56:45.0 Starting order split <34011855;01> knox_br 34011855
______^this line is the time

ok so here is my problem. I need two records, one for the start of the order(the earilest time) and one for the closing of the order(the latest time). I also need the time inbetween the two.

Thanks!

 

[Mike375]

Don't know about Excel but this is how I do it in Access.

Firstlly, I have the time entered as Now() as that gives a composite date/time

Lets say [a] is the earlier time/date and the later time/date

-[a] gives an answer in days

-[a]*86400/60 gives the difference in minutes

-[a]*86400/3600 gives the difference in hours

By using the composite time/date of Now it allows for the change of days. That is, it reads 2am as being later than 10pm on the previous date, in other words it will give a positive 4 hours

 

[homeguard]

i imported it into access. do you have any suggestions on how to sort for the first record and the last record of the order? some orders have 3-5 records in them so it makes it hard to get a good time.

 

[Mike375]

Not sure what you have. Perhaps you could make a copy of your data base, delete records but allow for some dummy records, compact and Zip.

If you have Now() entered then I would imagine each of the 5 orders will have a different time. A field with Now() entriest sorts on the combination of date/time.

In other words, for an A-Z sort it will put 24/1/08 11AM below 23/1/08 4PM