Sorting by "Difference" results between two Date/Time fields

**RitaB** · 04-05-2013, 02:01 PM

We have two date/time fields that we determine the number of hours between. Sometimes it may be a negative result, others not (eg. were you early or late?).

Timestamp A: 04/01/13 18:00
Timestamp B: 03/27/13 6:40

RESULT: Timestamp A - Timestamp B = 131.33 hours

We have tons of these calculations and we want to sort the table by the RESULTS from smallest time difference to largest.
When we sort by that column descending, it absolutely will not sort correctly and I now it has something to do with the fact that we're using date/time fields. I've always struggled with them!! If anyone can tell me how to:

1. determine the hour difference from one timestamp to the next, and
2. sort the dataset by the results of that column of calculations...

I would GREATLY appreciate it!!

Rita Brasher
Project Engineer
FedEx Express

**ChemistB** · 04-05-2013, 02:57 PM

If timestamp A is in col A and B is in B, in C

=(A2-B2)*24
You should be able to sort on Col C. What is not working? Can you upload a spreadsheet (Go Advanced> Manage Attachments)?

**Pepe Le Mokko** · 04-05-2013, 03:03 PM

If you have problems with dates and times, this link will help

**RitaB** · 04-05-2013, 03:19 PM

We figured out that the problem was using calculated date/time fields. She was adding time to existing fields, so Excel didn't like it. Once we "Copy/Paste Special...Values" and then did the calculations and sort, everything worked out perfectly...

Thanks for your replies, ChemistB and Pepe!!