Output of expired dates
 

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?


--
matteo
------------------------------------------------------------------------
matteo's Profile: http://www.excelforum.com/member.php...o&userid=24265
View this thread: http://www.excelforum.com/showthread...hreadid=378706

Something like this?

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

HTH
Ola Sandström


+-------------------------------------------------------------------+
|Filename: Book4.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3492 |
+-------------------------------------------------------------------+

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=378706

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


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

--
matteo
------------------------------------------------------------------------
matteo's Profile: http://www.excelforum.com/member.php...o&userid=24265
View this thread: http://www.excelforum.com/showthread...hreadid=378706

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+3 65*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 |
+-------------------------------------------------------------------+

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=378706