Output of expired dates

M

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?
 
M

matteo

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

+-------------------------------------------------------------------
|Filename: xlhelp.zip
|Download: http://www.excelforum.com/attachment.php?postid=3508
+-------------------------------------------------------------------
 
O

olasa

Here is one alternative to an Expire list:

Enclosed zip-file:
http://www.excelforum.com/attachment.php?attachmentid=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


+-------------------------------------------------------------------+
|Filename: xlhelp.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3514 |
+-------------------------------------------------------------------+
 
Top