Output of expired dates

[matteo]

I'm trying to create a list of expired items. I wonder if anyone could assist?
I'd like the list to show the column name and row name if something has expired.
Example: 3 dairy farmers (Jones, Smith, Barnes) and different products they produce (milk, cheese, cottage cheese, sour cream). Somehow, I'd like a list that compares Today() + varying timeframes (milk expiration + 7 days, cottage cheese lasting longer might be expired+14 days) and report back which person and product was expired.
Could that be done?

[olasa]

Something like this?

See enclosed zip-file: http://www.excelforum.com/attachment...tid=3492&stc=1

HTH
Ola Sandström

[matteo]

Fantastic response, but unfortunately I wasn't clear on what I was trying to do. The data format is more like the attached file

[olasa]

Here is one alternative to an Expire list:

Enclosed zip-file: http://www.excelforum.com/attachment...tid=3514&stc=1

Hope it can be of use.
Ola Sandström



Sample formulas:
=IF(TODAY()>IF(DAY(H3)=DAY(H3+365*2);H3+365*2;H3+365*2+1);"Expired "&TODAY()-IF(DAY(H3)=DAY(H3+365*2);H3+365*2;H3+365*2+1)&" days ago";"Will expire in "&IF(DAY(H3)=DAY(H3+365*2);H3+365*2;H3+365*2+1)-TODAY()&" days")

: Leap-year test: IF(DAY(H3)=DAY(H3+365*2)

=IF(SUMPRODUCT(--(ISNUMBER(FIND("Expired";B3:E3))));"Y";"")

{=IF(COUNTIF('Raw Data'!$F$3:$F$9;"Y")>=ROWS($A$4:$A4);INDEX('Raw Data'!C$3:C$9;SMALL(IF('Raw Data'!$F$3:$F$9="Y";ROW('Raw Data'!$F$3:$F$9)-ROW('Raw Data'!$F$3)+1);ROWS($A$4:$A4)));"")}

+ I included a PivotTable just as an example