I've got two lists of dates, say List A and List B. I want a function/formula to look at List B and return the number of days between that date in List B and the closet date prior to that in List A.

Example

Row        List A              List B            # Days
1          12/26/07	        01/08/08       13   (B1-A1)
2          01/16/08	        02/13/08       1     (B2-A5)
3          01/19/08		03/10/08       17   (B3-A7) 
4          02/01/08		04/17/08       ......
5          02/12/08		05/13/08
6          02/18/08		06/11/08
7          02/21/08		07/24/08

7/29/2010 4:38:06 PM

lemme google that for you

7/29/2010 4:45:52 PM

http://www.theexceladdict.com/_t/t040303.htm

=DATEDIF(A1,A2,"d").

7/29/2010 4:53:11 PM

not sure if that will work since I need to function to look through column A to find the closest previous date... and it seems as though excel 2010 doesn't have datedif undocumented!

I mean, I know how to return the number of days between dates, I'm just not sure of a function to find the closest previous date to the one specified from List B

[Edited on July 29, 2010 at 5:03 PM. Reason : .]

7/29/2010 4:57:19 PM

in column C type

=MAX(IF(B1>A:A,A:A,0)) and press ctrl+shift+enter. you should end up with {=MAX(IF(B1>A:A,A:A,0))} the { } indicate an array formula result

then in column D type

=B1-C1

not sure if there's a way to combine to one column

7/29/2010 5:04:44 PM

yeh, I've been playing with the max(if( and arrays, but I keep getting the false value returned (0)

shit, just figured out why nothing is working... for some reason, it's not picking up List A as dates... hmm, time to redo those

ah, two digit year FTL

thank you left mid and right!

[Edited on July 29, 2010 at 5:19 PM. Reason : .]

7/29/2010 5:10:23 PM

try converting ur dates to numbers and then back?

7/29/2010 6:19:11 PM

could you post your formula once its done. definitely interested in how you did this for my future knowledge

7/29/2010 6:51:41 PM

^^nah, I had to parse out each date section using left, mid, and right then put the date back together and adding 20 to get a 4 digit year. might be another/easier way, but this worked and was easy.

^will do, working on typing it up on here

example:

*be sure to crtl+shft+enter to create an array formula in Col C (which will add { } to the formula)
**I used Round() in Col D in case I ever had dates with times (though in my data set, they shouldn't)
***I used >= in Col C so that matching dates would show # days = 0

   List A         List B        Closest Previous Date                      # Days
      A               B                          C                             D
1   07/14/07	07/26/07     =MAX(IF(B1>=$A$1:$A$9,$A$1:$A$9,"error"))       Round(B1-C1,0)
2   09/12/07	08/16/07     =MAX(IF(B2>=$A$1:$A$9,$A$1:$A$9,"error"))       Round(B2-C2,0)
3   09/20/07	10/11/07     =MAX(IF(B3>=$A$1:$A$9,$A$1:$A$9,"error"))       Round(B3-C3,0)
4   10/25/07	12/10/07     =MAX(IF(B4>=$A$1:$A$9,$A$1:$A$9,"error"))       Round(B4-C4,0)
5   11/15/07	01/08/08     =MAX(IF(B5>=$A$1:$A$9,$A$1:$A$9,"error"))       Round(B5-C5,0)
6   11/26/07	02/13/08     =MAX(IF(B6>=$A$1:$A$9,$A$1:$A$9,"error"))       Round(B6-C6,0)
7   12/15/07	03/10/08     =MAX(IF(B7>=$A$1:$A$9,$A$1:$A$9,"error"))       Round(B7-C7,0)
8   12/21/07	04/17/08     =MAX(IF(B8>=$A$1:$A$9,$A$1:$A$9,"error"))       Round(B8-C8,0)
9   12/26/07	05/13/08     =MAX(IF(B9>=$A$1:$A$9,$A$1:$A$9,"error"))       Round(B9-C9,0) 

7/29/2010 6:56:52 PM