How days since last visited

  • Thread starter Thread starter Pablo
  • Start date Start date
P

Pablo

I am trying to figure out how to determine how days it has been since someone
last visited. I have a weekly attendance sheet that listed a class roster in
column A3:A10. Running across the top D2:I2 are class dates 1/04/09, 1/11/09,
1/18/09, 1/25/09, 2/02/09... When someone attends a 'X' is placed in the cell.

So if someone's last attendance was 1/04/09 the formula would return 13.

Any ideas?
 
mine is a slightly more complicated solution as cd. to that of Biff

=IF(COUNTIF(D3:I3,"x"),TODAY()-OFFSET($C$2,,MAX(IF(D3:I3="x",COLUMN
(D3:I3)-1,""))),"no attendances")

CTRL+SHIFT+ENTER this formula cause this is an array formula
 
What does "zzzzz" represent?

zzzzz is the lookup_value. This may be hard to understand....

If the lookup_value is greater than any value in the lookup_vector the
formula will return the result that corresponds to the *last text* value in
the lookup_vector that is *less than* the lookup_value. You're entering an
"x" to denote attendance. "X" is less than "zzzzz" so the formula "finds"
the *last* "x" in the range and uses the corresponding date.
is there a way to use a HLookup?
Click to expand...

No. With HLOOKUP, you would lookup a date and return data from the rows
below the date headers. In your application you have to find the last "x"
before you can find the corresponding date.
 
Back
Top