Formula for getting latest date for an entry

Question

This is probably a bit of a noob question, but I've been using a very (very!) simple sheet to keep track of the hymns we sing at our church, and how often, and that's been easy to set up. But what would be really helpful is another column that says when each hymn was last sung (so we know if it was sung only two weeks ago then we probably shouldn't do it again, but if it was 6 months ago or something then it's probably worth singing it again.)

I've attached the current table. This is probably a terrible way of recording this data and there's probably a far more efficient way of doing it, but it works for us at the moment! But if you were able to help fill in column C then I'd be very grateful.

https://docs.google.com/spreadsheets/d/1ruZ5QLMXXoTxwwg-BtceFJ5m2wRLWLXTV7KxJttFFSQ/edit?usp=sharing

Answer 1

this formula gets always the date when each hymn was last sung and works with cells that mix date and text

=iferror(LOOKUP(1, D2:2, $D$1:$1))

The formula is draggable as is. If the song was never sung, returns an empty result. See for example:

1. God is Working His Purpose Out
2. Lift high the cross
3. Songs of Thankfulness and Praise

Answer 2

the formula for column C should be:

=maxifs(D1:V1,D2:V2,1)

It calculates the largest number in the range D1:V1 where D2:V2 equals one.

You will also need to format column C as dates, and if you copy it down to other cells, make sure that the ranges are locked with the $ operator ($D$1:$V$1). For the formula to work well, all content in row 1 should be dates - the formula won't work with cells that mix date and text like "14/4/22 (Maundy Thursday)"

Answer 3

If you have all of your Sundays for the year mapped out, you can hit it with this one formula in C2 to give you the latest date (even if it has the holiday in brackets)

=ARRAYFORMULA(
  IF(ISBLANK(A2:A),,
   IFERROR(
    VLOOKUP(
     A2:A,
     QUERY(
      SPLIT(
       FLATTEN(
        IF(ISBLANK(D2:Z),,
         A2:A&"|"&
         IFERROR(
          INDEX(
           TRANSPOSE(
            SPLIT(
             TRANSPOSE(D1:Z1),
             "(")),
           1,0)))),
       "|"),
      "select Col1, Max(Col2)
       where Col2 is not null
       group by Col1"),
     2,FALSE))))

Essentially, its replacing the 1s with the extracted date from the header row and prepending the song with a pipe as a delimiter. It then FLATTENs and SPLITs this and runs the two columns through a QUERY to return the MAX value for each song.

This creates the lookup table for the VLOOKUP, which we'll use to realign the MAX dates to its respective title.

If you have your sundays all mapped out, your range will be D2:BD and B1:BD1, or somewhere around there.

I can't reply to @Daniele, but that formula wrapped with a split to clean it up will spit out a valid date

=INDEX(IFERROR(SPLIT(LOOKUP(1, D2:2, $D$1:$1),"(")),0,1)

This is an excellent approach.