Forum Discussion

Steven Schiltz's avatar
Steven Schiltz
Copper Contributor
May 21, 2018

Formula for latest date in a series of dates

Hi - I have a spreadsheet with dates that we expect materials to arrive from different vendors on a project.  Hoping to add a formula that looks at a series dates in non-sequential columns and adds t...
Formulas & Functions
Need Help
Matt Mickle's avatar
Matt Mickle
Bronze Contributor
May 21, 2018

Steven-

 

Can you please provide a non-sensitive example file so the community can better understand how the data is stored/represented.  The more detail you can provide the better chance someone in the community will be able to assist you.

  • lkm0707's avatar
    lkm0707
    Copper Contributor
    May 23, 2018

    THIS IS WHAT THE SPREADSHEET LOOKS LIKE.  WHEN I SEND OR RECEIVE THE DOCUMENTS THE DATE IS CALCULATED BY TAKING THE "PROUPONDED DATE" AND ADDING 35 DAYS TO THE "RESPONSE" DATE.  IF THERE IS NO VALUE IN THE "PROPOUNDED" DATE FIELD I GET THE "02/01/00" DATE.   IT MAKE THE SHEET BUSY AND I ONLY WANT TO SEE THE ACTUAL "RESPONSE" DATE THAT'S CORRECT.  EXAMPLE WOULD BE PEMBERTON - NOTICE THE DATES ARE DIFFERENT

     

    EYMANN-TAYLOR       2/4/002/4/002/4/002/4/002/4/00
    Poole, David       2/4/002/4/002/4/002/4/002/4/00
    Poole, Caren       2/4/002/4/002/4/002/4/002/4/00
    Kaur, Gurdip       2/4/002/4/002/4/002/4/002/4/00
    Pemberton, RonINSRUANCE SOLUTIONS, INC. 10/4/17    2/4/0011/8/172/4/002/4/002/4/00
    Pemberton, RonNAVO FINANCIAL      2/4/002/4/002/4/002/4/002/4/00
    Pemberton, RonZACHARY NAVO      2/4/002/4/002/4/002/4/002/4/00
            2/4/002/4/002/4/002/4/002/4/00
    • Matt Mickle's avatar
      Matt Mickle
      Bronze Contributor
      May 23, 2018

      What about trying something like this (See attached .xlsx file for reference):

       

      Use This formula in I2 then drag right and then down:

      =IF(D2<>"",D2+35,"")

       

      If this isn't quite what you're looking for please let me know where I went wrong and I'll give it another shot.

       

      • lkm0707's avatar
        lkm0707
        Copper Contributor
        May 24, 2018

        I already have the formula in the appropriate cells I'm just trying to "HIDE" the dates that will not have a value until an actual date is entered in the "PROPOUNDED" columns that will auto-calc the 35 days they have to respond to our request.  I know there is a way to not display cells that have a "0" value I thought as advanced Excel is there would be a way to do this.  In other words, I don't want anything to appear in the cells UNTIL I enter a source date.

         

        Does that make sense???

Resources