Sumproduct Dates

  • If in a series of dates and corresponding cash flows (say: 12/31/2018 - 12/31/2033) you want to use sumproduct to return the cash flow for a date not in the series (say: 12/31/2016), can sumproduct return the cash flow ties to 12/31/2018 (oldest and therefore closest date)?

  • Hello,


    Say your dates are listed in range A2:A17


    in cell B2 =DATE(2016,12,31)


    In order to get the closest date ...you could have in cell C2 the following Array Formula


    Code
    1. =MIN(IF(A2:A17>B2,A2:A17))


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)