# How to calculate time differences with condition

1. ## How to calculate time differences with condition

I have a table with 2 columns consists of: TIME of order and CUSTOMER name. I need to find the time difference between orders for a specific customer.

TIME CUSTOMER
12/30/2015 23:59:53 STY
12/30/2015 23:50:44 MCL
12/30/2015 23:47:10 STY
12/30/2015 22:30:41 STY
12/30/2015 22:21:20 MCL
12/30/2015 21:53:36 STY
12/30/2015 21:18:32 CHR
12/30/2015 21:07:36 MCL
12/30/2015 20:55:36 CHR
12/30/2015 19:21:49 STY
12/30/2015 18:04:31 MCL
12/30/2015 15:57:28 CHR

From the table above, I have to create report on time difference for specific customer. Sample of result is as follow:

CUSTOMER STY

**TIME CUSTOMER PERIOD**
12/30/2015 23:59:53 STY 00:12:43
12/30/2015 23:47:10 STY 01:16:29
12/30/2015 22:30:41 STY 00:37:05
12/30/2015 21:53:36 STY 02:31:47
12/30/2015 19:21:49 STY 19:21:49

CUSTOMER MCL

**TIME CUSTOMER PERIOD**
12/30/2015 23:50:44 MCL 01:29:24
12/30/2015 22:21:20 MCL 01:13:44
12/30/2015 21:07:36 MCL 03:03:05
12/30/2015 18:04:31 MCL 18:04:31

or show all customers:

CUSTOMER ALL

**TIME CUSTOMER PERIOD**
12/30/2015 23:59:53 STY 00:09:09
12/30/2015 23:50:44 MCL 00:03:34
12/30/2015 23:47:10 STY 01:16:29
12/30/2015 22:30:41 STY 00:09:21
12/30/2015 22:21:20 MCL 00:27:44
12/30/2015 21:53:36 STY 00:35:04
12/30/2015 21:18:32 CHR 00:10:56
12/30/2015 21:07:36 MCL 00:12:00
12/30/2015 20:55:36 CHR 01:33:47
12/30/2015 19:21:49 STY 01:17:18
12/30/2015 18:04:31 MCL 02:07:03
12/30/2015 15:57:28 CHR 15:57:28

It happens that we monitor our customer transactions in seconds or minutes. So whenever there is no transaction at all for a specific customer in hours or days, it signals that we have problem to be addressed asap. This report is used to show why a certain customer doesn't put order as frequently as before. Big time difference means a customer doesn't put order for a long period of time. It will also help us detect if there is a serious interruption in our service level.

For "ALL customer" report, it will detect interruption of our service when we receive no order at all for a period of time.

2. ## Re: How to calculate time differences with condition

For All customers:

in C2 and copy down

Formula:
`Please Login or Register  to view this content.`

with Customer name in D2 enter array formula in E2 and copy down

Formula:
`Please Login or Register  to view this content.`

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

in F2

Formula:
`Please Login or Register  to view this content.`

 v A B C D E F 1 Date/Time Customer CUSTOMER ALL Unique Customer Results 2 12/30/2015 23:59 STY 0:09:09 STY 12/30/2015 23:59:53 0:12:43 3 12/30/2015 23:50 MCL 0:03:34 12/30/2015 23:47:10 1:16:29 4 12/30/2015 23:47 STY 1:16:29 12/30/2015 22:30:41 0:37:05 5 12/30/2015 22:30 STY 0:09:21 12/30/2015 21:53:36 2:31:47 6 12/30/2015 22:21 MCL 0:27:44 12/30/2015 19:21:49 19:21:49 7 12/30/2015 21:53 STY 0:35:04 8 12/30/2015 21:18 CHR 0:10:56 9 12/30/2015 21:07 MCL 0:12:00 10 12/30/2015 20:55 CHR 1:33:47 11 12/30/2015 19:21 STY 1:17:18 12 12/30/2015 18:04 MCL 2:07:03 13 12/30/2015 15:57 CHR 15:57:28

3. ## Re: How to calculate time differences with condition

It worked very well! For the customer name, how can I make a drop down list linked to the formula in E2 and F2? Thanks.

4. ## Re: How to calculate time differences with condition

You need to make a drop-down list in D2 so you can select a other customers. The formulas will display there results.

Please see attached file with a drop-down list in D2.

There are currently 1 users browsing this thread. (0 members and 1 guests)