Function indicating last use of 2 different values

[rluesc]

Column A contains dates. Columns B to D are exercises. Whenever I complete an exercise on a certain date, I either input 1 or 2 in the respective cell. For example, I do the exercise “back” on 9/7/2015, then I insert 1 or 2 in B6.

What I need now are two separate functions for each exercise or columns. Each of these functions should indicate on which date I have last inserted either 1 or 2. So, if I insert 2 in column “back” on 9/7/2015, the function should yield 9/7/2015. If I insert 1 on 9/10/2015 in column “back”, it should yield 9/10/2015. I only want one column for each exercise.

The function in row 2 has served me well while I only used a single value but does not work with multiple values (e.g. 1 and 2) in one column.

[Jacc]

Try this in B2:

Please Login or Register  to view this content.

[FDibbins]

If you are ONLY entering 1 or 2, then pretty much, you are looking for the very last entry in that column. Try this B2, copied across...
=INDEX($A$4:$A$14,MATCH(0,B$4:B$14,-1))

[Jacc]

A little bit shorter version here:

Please Login or Register  to view this content.

[rluesc]

Thanks for the help. I've attached another sample file.

Try this in B2:

Please Login or Register  to view this content.

I've entered this formula in B2. As you can see, it correctly states the date of the last entry with the value 1. However, I need the same formula now displaying the last entry for the value 2 in the same column.

If you are ONLY entering 1 or 2, then pretty much, you are looking for the very last entry in that column. Try this B2, copied across...
=INDEX($A$4:$A$14,MATCH(0,B$4:B$14,-1))

I've used your formula in C2. It yields 42261 as a result, which does not help.

A little bit shorter version here:

Please Login or Register  to view this content.

See D2. Yields 42256. Maybe a problem with the format in the date cells?

[Tony Valko]

I didn't download your file.

Try formatting the formula cells as Date.

In Excel dates are really just numbers formatted to look like dates.

The numbers you see, 42261 and 42256, are called the date serial numbers. When formatted as Dates, you get:

42261 = 9/14/2015
42256 = 9/9/2015

The date serial number is the count of days starting from a base date. In Windows Excel the default base date is January 1, 1900. So, January 1, 1900 is date serial number 1.

Jan 1 1900 = date serial number 1
Jan 2 1900 = date serial number 2
Jan 3 1900 = date serial number 3
Jan 4 1900 = date serial number 4
Jan 5 1900 = date serial number 5
etc
etc
Jan 1 1975 = date serial number 27395
Jan 1 2000 = date serial number 36526
Jan 1 2015 = date serial number 42005

You can see the date serial number by entering some date in a cell and then changing that cells format to General or Number.

[rluesc]

Thanks for the clarification regarding the date format.

So the only issue remaining now is that all functions yield the last value entered in a column, be it 1 or 2. However, I need one function reading ONLY the entries with value 1 and another function reading ONLY the entries with value 2. Hope that makes sense.

[bebo021999]

So B2 for 1 and B3 for 2?

B2=IF(MAX(INDEX($A$4:$A$14*(B$4:B$14=1),))=0,"",MAX(INDEX($A$4:$A$14*(B$4:B$14=1),)))

Change 1 to 2 for B3

[rluesc]

That's it! Works like a charm. Thanks to all the contributors.

[Jacc]

Ah, two functions. Well then you can just split up mine, it is shorter.
In B2:

Please Login or Register  to view this content.

In B3:

Please Login or Register  to view this content.