+ Reply to Thread
Results 1 to 3 of 3

Calculate number of days between sales and prior sales date

  1. #1
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,173

    Calculate number of days between sales and prior sales date

    In column A is a list of car sales
    In column B is the date of the sale

    I tried using a vlookup but that grabs the first date in the list and I am trying to grab the second to last date in the list. I have attached a sample spreadsheet with desired output. Thanks any advance for any comments.

    An example would be

    Column A ...........Column B
    Ford...................3/1/13
    Buick..................3/8/13
    Ford...................3/9/13
    Ford...................4/4/13
    Buick..................6/6/13
    Ford...................7/1/13

    I'm trying to figure out the days elasped between sales for same make of car. Desired output would be:

    Column A ...........Column B..............Prior Sales Date (col C)............days between sales (col D)
    Ford...................3/1/13.................n/a; first sales date.................n/a; first sales date
    Buick..................3/8/13.................n/a; first sales date.................n/a; first sales date
    Ford...................3/9/13.................3/1/13....................................8 days
    Ford...................4/4/13.................3/9/13....................................26 days
    Buick..................6/6/13.................3/8/13....................................90 days
    Ford...................7/1/13.................4/4/13....................................88 days

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Calculate number of days between sales and prior sales date

    In C3

    =IF(MAX(INDEX(($A$2:$A2=A3)*$B$2:$B2,0)),MAX(INDEX(($A$2:$A2=A3)*$B$2:$B2,0)),"First instance")

    Copy down
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Calculate number of days between sales and prior sales date

    C2: =IF(COUNTIF(A$2:A2,A2)=1,"First instance",LOOKUP(2,1/(A$1:A1=A2),B$1:B1))

    copy down as far as needed

+ 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. Replies: 7
    Last Post: 07-23-2013, 07:56 AM
  2. Replies: 8
    Last Post: 02-14-2013, 03:45 PM
  3. [SOLVED] Calculate total sales adjustments for each sales person
    By Adama in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-02-2012, 06:26 PM
  4. Replies: 2
    Last Post: 07-21-2010, 12:54 PM
  5. formula to calculate sales tax from total sales
    By Deanna in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-05-2005, 04:05 PM

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