Expiry Date Excel sheet!

[Emperor]

Hey all,



Im always amazed at the ability of humans to find solutions for problems and their ability to improve everything. Im looking for something like that here too.

I am a complete beginner when it comes to excel but it fascinates me with the things it can achieve.

currently i am working for a company that among other things sells food and stuff with an expiry date.

So far the way the company handles this is by having an excel sheet with the list of the products and a possible expiry date that i have to manually search per product. every month i get a new list that i have to check over again from scratch.

I wanted to make it a bit more interactive and progress a bit things by changing the sheet slightly... ok maybe not slightly... ill go Frankenstein on it.


Ok so heres what im thinking:
Ill have one excel page that includes
column 1: product number (easy to search the product by that in store)
column 2: product name and description
column 3: number of days close to current day (using the TODAY() function
Column 4-10 possible expiery dates (yes some products have maaaany expiery dates like Coka~Cola can have 3+ different per month)

After I complete the expiry dates on the columns 4+ i would like to create a space on another page, if possible, that shows all the dates that are 20 days or closer to today. (or at least the 20 closest expiery dates from the columns 4-10 of the first page)

Think thats possible?
Also any ideas of how to make it better are welcome
Thank you for taking the time to read through!

[macropod]

If you have the expiry dates or the # days remaining in a single column, you can use Excel's filtering tools to filter that column according to whether the date/days falls within your 20-day parameter. No need for an extra sheet. Conditional formatting could also be used to automatically highlight items with less than 21 days remaining.

[Emperor]

Hmmm nice thinking, simple and effective.

Id prefer to keep the spreadsheet in numerical order of the product number, thats why i was wondering if theres a way to select the expiry date columns and make the closest dates appear in other selected columns (other page or other part of same page).

but thank you, if i cant find what im looking for this is the best solution so far!

[macropod]

Filtering has no effect on the order in which the data are stored; it sounds like you're confusing filtering with sorting.

[ufopilot]

Maybe this would help a little - at least get you on the right track

[Emperor]

Dude! you're a legend this is exactly what i was looking for!

By giving it a look i can see how it works (more or less) and I think that I can now solve another problem I have encountered trying to display numbers.

If I have 0 stock of a product there will be no expiry date in ANY box of that product... and of course that would return the numerical value of 0 - the numerical value of the current date, correct?
[so for right now 27/10/15 and time 23:34, the "days left" box returns the value of "-42304" ]

So Ill probably need to include a bit more of an "IF" statement indicating that if there is no value in the boxes targeted, it should return nothing. (am I on the right thinking here?)

[ufopilot]

I have added conditional formatting to the rows/cells that have "0" as an expiration date (sheet2 will give you a little hint on how to do this). You can also use this to conditional format the column so that the wording "No Exp. Date" will show up is a different color.
In order to show this wording, I had to change the formula to start with summing each row of expiration dates - when the sum is then zero (0), it will result in the wording, if not, then it will continue to do as the formula before did. - this means that each cell it sums in a particular row, will need to read zero. Hope this helps


I had to split the two sheets, deleted some columns in the first and save one as a JPG, as the file size was too big to attach. Maybe you can enlarge the JPG to view.