+ Reply to Thread
Results 1 to 4 of 4

How to calculate time differences with condition

  1. #1
    Registered User
    Join Date
    01-27-2013
    Location
    jakarta
    MS-Off Ver
    Excel 2013
    Posts
    12

    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. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How to calculate time differences with condition

    For All customers:

    in C2 and copy down

    Formula: copy to clipboard
    Please Login or Register  to view this content.


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

    Formula: copy to clipboard
    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: copy to clipboard
    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
    Attached Files Attached Files
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    01-27-2013
    Location
    jakarta
    MS-Off Ver
    Excel 2013
    Posts
    12

    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. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    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.
    Attached Files Attached Files
    Last edited by AlKey; 02-01-2016 at 11:15 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Need your help to calculate the differences with a formula
    By NitinSatale1986 in forum Excel General
    Replies: 1
    Last Post: 05-14-2015, 09:25 AM
  2. Calculate FX differences
    By Yzori in forum Excel General
    Replies: 1
    Last Post: 06-17-2014, 10:22 PM
  3. Formula to calculate time differences but not all cells have values?
    By claudiamariep in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-21-2014, 03:23 PM
  4. [SOLVED] Calculate time in days from current with special condition
    By Nairobi Nice in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-19-2012, 10:26 AM
  5. How to calculate Date & Time differences
    By robs in forum Excel General
    Replies: 8
    Last Post: 10-05-2005, 10:05 PM
  6. How to calculate Date & Time differences
    By robs in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-04-2005, 11:22 AM
  7. Calculate differences between age
    By carlharris in forum Excel General
    Replies: 1
    Last Post: 10-03-2005, 12:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1