View original (French)

Retrieve a date in Google Sheets

gaelgael228 Posted messages 26 Status Member -  
 Anonymous user -
Hello,

In my spreadsheet, I align dates one after the other (in rows and in ascending order).
I would like to access the most recent date (the one furthest to the right) without having to scroll through the spreadsheet.

In short, I want to retrieve the most recent date and display it in a cell at the beginning of the spreadsheet (always the same one).

Do you know how to do this?

I am a beginner in this area.
Thank you

Configuration: Windows / Chrome 95.0.4638.54

5 answers

  1. Anonymous user
     
    Hello,

    The formula =MAX(data_range) will return the most recent date.
    1
  2. gaelgael228 Posted messages 26 Status Member
     
    Great, thank you very much, it's working!

    Provided that I can also apply this to blank lines. At that point, it gives me an absurd date "30-12-1899"...

    Is it possible to exclude blank lines (return nothing when the lines are empty)?
    0
    1. Anonymous user
       
      It's strange because the MAX function does not take empty cells into account.

      I did a test with Sheets to ensure that the behavior was not different from Excel, and I do get the max date from a range with one or more empty cells!

      Could we have an example of your file?
      0
  3. gaelgael228 Posted messages 26 Status Member
     
    Hello, yes, with pleasure.

    here it is https://docs.google.com/spreadsheets/d/1if7yZfznu_OhAfbyvuQsOMo75J1RdMifLlF3XTfVF5M/edit?usp=sharing

    I deleted all data from the document except for the relevant columns, namely:
    column P which retrieves data from columns AK to AP

    As you will see, the formula works well, but when there is no date on the row of the formula, I get returned an absurd date.
    0
    1. Anonymous user
       
      Hello,
      There are restrictions on this sharing: one cannot access it just by having the link. Could you loosen the access?

      Best regards
      0
  4. gaelgael228 Posted messages 26 Status Member
     
    Oops sorry,
    I think it's good now
    https://docs.google.com/spreadsheets/d/1if7yZfznu_OhAfbyvuQsOMo75J1RdMifLlF3XTfVF5M/edit?usp=sharing
    0
    1. Anonymous user
       
      No worries :-) It's readable now.

      Using formulas in Sheets infrequently, I don’t understand where the problem comes from.
      To work around this, I suggest using the formula =IF(max(a_range)>DATEVALUE("01/01/1980"),max(a_range),"") (see column O of your sheet).

      Maybe someone will suggest something better!

      Best regards
      0
  6. gaelgael228 Posted messages 26 Status Member
     
    Great Roma, that works!
    Thank you very much!
    0
    1. Anonymous user
       
      You're welcome :-)
      Apparently, 30-12-1899 would be the minimum date handled by Sheets.
      0