3 Replies Latest reply on Aug 14, 2012 1:10 PM by Mark Holtz

# How do I identify and label records based on time?

I need to categorize a list of trip records based on the time difference between a trip's scheduled dropoff time and it's actual dropoff time.

First, here are the categories:

Early = Actual Dropoff Time is 10:01 minutes BEFORE the Scheduled Dropoff Time

On Time = Actual Dropoff Time is less 10:01 minutes BEFORE the Scheduled Dropoff Time AND less LESS than 10:01 minutes AFTER the Scheduled Dropoff Time

Late = Actual Dropoff Time is 10:01 minutes AFTER the Scheduled Dropoff Time

Next, here is my very crude attempt at creating a calculated field to generate the desired results.

IF [ActualEndDateTime - ScheduledDropoffDateTime] >= 00:10:01 THEN 'Late' ELSEIF [ActualEndDateTime - ScheduledDropoffDateTime] <= 00:10:00 AND <= -00:10:01 THEN 'On Time' ELSE 'Early' END.

Obviously, I know the formula is very flawed but I wanted to do my best to show where my thinking was for solving this problem. Any helpful suggestions would be most appreciated.

I have attached a packaged workbook of my efforts if anyone wants to give this challenge a try.

Thanks - William

• ###### 1. Re: How do I identify and label records based on time?

Hi William,

A calculated field with this logic should get you what you're after.

10 minutes = 600 seconds.

IF DATEDIFF('second',[ScheduledDropoffDateTime],[ActualEndDateTime]) <-600

THEN 'Early'

ELSEIF DATEDIFF('second',[ScheduledDropoffDateTime],[ActualEndDateTime]) <600

THEN 'On Time'

ELSE 'Late'

END

I went ahead and added a calculated field showing the result of the datediff function too in the attached workbook.

• ###### 2. Re: How do I identify and label records based on time?

Perfect! Thank you Mark. This is exactly what I was trying to accomplish. And my very crude formula looked nothing like the formula I actually needed.

If I am not pushing my luck, could you tell me why I cannot re-sort the trip data by scheduled pickup time in both my original worksheet and your modified worksheet? I just wanted to show the trip times from earliest to latest.

Thanks --- William