Google Sheet: Pick max date out of row

0
votes

Assume: in a google sheet the attendance to courses is logged with crosses.

Goal is to get the last attendance date of a person and add 6 months to it. ( course needs to be followed every six months ).

I've tried to retrieve the date of the last course in one column and add the +6months immediately and tried to have the +6 months in the second column, but I didn't succeed in both attempts.

See this link for example google sheet: https://docs.google.com/spreadsheets/d/10DG2I4VgTlOHJ5TG0qH4pJ5hJ9zsYuJCgRzEtuiTE60/edit?usp=sharing

1
dategoogle-sheetsmaxdate
I improved the wording of your question, but you should read minimal reproducible example and make further updates yourself, for example by including the code/formulas you wrote yourself. Linking to off-site pages is not an option here. Questions should be self-contained, not require the reader to click links! – GhostCat

1 Answers

0
votes
=lookup("x",$B4:$F4,B$2:F$2)

Enter this formula in cell G4 and copy down the rows.
This formula looks for "x" (the attendance indicator) in cells in row 4 (columns B to F) - it's looking for the last instance. Then it returns the cell value from row 2 (the course date) of the relevant column.

=EDATE(G4, 6)

Enter this formula in cell H4, and copy down the rows.
This is pretty straightforward. It takes the date obtained from the lookup formula and adds 6 months.

Note that the lookup formula references column F. That's because as you create more courses you'll want the formula to automatically include them. So in cell F1, type something like "Don't use this column" - and then don't use that column!

Now when you add a new course, insert a new column to the left of the column headed "Don't use this column". This will ensure that the range in the lookup formula will expand to include the new column/course, and your latest attendance date will be updated automatically.

BTW, there's no guarantee that the date of the next course is a working day.