Posted: 2/8/2009 8:51:01 AM EDT
|
I use Microsoft Excel to inventory all my survival stuff and long term food. I wanted a way where at a glance I could tell if anything was expired. In Excel I created a column in front of the items in the list. In that new column, in front of each item, put the following line of code. Edit as needed, the date is in the form Year, Month, Day. It will display Good until that date passes, then it will display Expired.
=IF(NOW()>DATE(2015,2,1),"EXPIRED","GOOD") |
| I've actually used conditional formatting alot to color-code things as they near expiration. I fire up the sheet and the colors grab my attention immediately and I know what to use/donate before they go "bad". Best-by > 90 days from TODAY(), no formatting. Best-by within 90 days of TODAY(), green font on yellow background. Best-by within 60 days of TODAY(), red font on yellow background. Best-by >= TODAY() and it goes red. |
|
The USB bar code scanners work well, but I dont know how the UPC code decodes, into a digit, or actual alphanumerics.
If it decodes into an alphanumeric, you would have to look it up somewhere to decode it to "Campbells Tomato soup, 8oz" or whatever. You just plug in the usb code scanner and it inserts the data into the excel 'cell' you are currently in when you press the trigger. |
|
Quoted:
The USB bar code scanners work well, but I dont know how the UPC code decodes, into a digit, or actual alphanumerics. If it decodes into an alphanumeric, you would have to look it up somewhere to decode it to "Campbells Tomato soup, 8oz" or whatever. You just plug in the usb code scanner and it inserts the data into the excel 'cell' you are currently in when you press the trigger. If it decodes on alphanumeric you could enter it on an inventory page (and manually type the name in another column) and use a pivot table on page one to do the actual managing... Pivot tables can be a PITA to use but when you "get it" it's the real power behind excel :p |
|
Quoted: Quoted: The USB bar code scanners work well, but I dont know how the UPC code decodes, into a digit, or actual alphanumerics. If it decodes into an alphanumeric, you would have to look it up somewhere to decode it to "Campbells Tomato soup, 8oz" or whatever. You just plug in the usb code scanner and it inserts the data into the excel 'cell' you are currently in when you press the trigger. If it decodes on alphanumeric you could enter it on an inventory page (and manually type the name in another column) and use a pivot table on page one to do the actual managing... Pivot tables can be a PITA to use but when you "get it" it's the real power behind excel :p This is why I come here. I had never thought of doing either of these. 
|
|
Quoted:
The USB bar code scanners work well, but I dont know how the UPC code decodes, into a digit, or actual alphanumerics. If it decodes into an alphanumeric, you would have to look it up somewhere to decode it to "Campbells Tomato soup, 8oz" or whatever. You just plug in the usb code scanner and it inserts the data into the excel 'cell' you are currently in when you press the trigger. Hummmmm. I work on this stuff all the time but I've never had time to program the app. for preps. I need to do that soon. UPC database look here: http://www.upcdatabase.com/downloads/ Also might want to look at a database system.. M$oft Access or Open Office etc.. |
|
Quoted:
Quoted:
The USB bar code scanners work well, but I dont know how the UPC code decodes, into a digit, or actual alphanumerics. If it decodes into an alphanumeric, you would have to look it up somewhere to decode it to "Campbells Tomato soup, 8oz" or whatever. You just plug in the usb code scanner and it inserts the data into the excel 'cell' you are currently in when you press the trigger. Hummmmm. I work on this stuff all the time but I've never had time to program the app. for preps. I need to do that soon. UPC database look here: http://www.upcdatabase.com/downloads/ Also might want to look at a database system.. M$oft Access or Open Office etc.. I found a USB scanner for 42$ but thought of something... Even if cans have the same UPC they don't have the same production date / expiration date... |
|
I've been wanting to ask about the code scanner for a while, just never remembered.
I hadn't thought of putting in the expiration date, which would be a nice feature. I wanted to keep track of name, quantity and the last few prices I paid. Sort of an easy reference for the best sale price. I keep receipts for reference, but it's a pain. I own Excel, but have never used it. I don't even know where to start. I'm starting to accumulate alot of crap and I need to start some sort of inventory system before it gets too big. |
|
Thanks for the formula. You guys are full of great ideas. I'm going to use your date formula for my prescriptions and the animals so I have time to get new stuff when needed.I've been keeping track of my food using Julian dates and conditional formatting mainly to keep up my Excel skills since I retired. I also have a spreadsheet for all my ammo with a date of manufacture or purchase date with conditional formating for the age so if I go to do some shooting I can use the oldest first.
I use the NM Collector software for my guns and it can use barcodes. I bought the tags to print out with basic info on each gun including barcodes so I need to get with it and create them. It sure would be nice to have all guns,bayonets and ammo cans barcoded. Red |
|
how much interest would there be in getting your grocery "tape" as a file that could be imported into database or spreadsheet of your choice? or if you had access to all of the shopping data that your grocery card shopper card contained.
...also looking into possibility of using iPhone as bar code scanner. |
...
|
I did find a barcode/scanner based Excel spreadsheet for inventory tracking, but it will not support some of the other functions like use by dates, etc. It is geared solely for inventory (in/out) functionality.
If there are some Excel experts out there, perhaps it can be built upon to become a ARFCOM App 
PM me if you are the said expert, and I'll email you a copy. c0 ____________________________ Failure to plan on your part does not constitute an emergency on mine. Tpass.org |
|
Quoted:
I did find a barcode/scanner based Excel spreadsheet for inventory tracking, but it will not support some of the other functions like use by dates, etc. It is geared solely for inventory (in/out) functionality. If there are some Excel experts out there, perhaps it can be built upon to become a ARFCOM App
PM me if you are the said expert, and I'll email you a copy. c0 ____________________________ Failure to plan on your part does not constitute an emergency on mine. Tpass.org kicking this up in case you didnt get my pm |
Message Sent 
c0 ____________________________ Failure to plan on your part does not constitute an emergency on mine. Tpass.org |
|
Quoted:
Great thread...I need to do this. I also am getting around to doing inventory with other items including GHB's, BOB's, vehicle kits, etc. I have done quite extensive work with Excel and Access. IMO Access can do anything (and much, much, much, more than Excel). Excel is more user friendly to setup, easier to make small changes, but eventually you'll have 10+ "sheets" and making a small change such as a change in a state sales tax will have to be replicated 10 times or more. Whereas, Access you can set it up once and if properly setup be expanded on rather easily. Basically I think Excel is good for small projects, but at some point you should really bite the bullet and learn Access and some MS Basic. Learn about how to properly structure databases (allowing for future changes), then setup some sweet forms and reports. Heck you could even setup Access to email you when a supply needs to be used/thrown away. The possibilities are endless. Access for the win! |
|
Quoted:
I always tell folks that, while Geek is not my native language, I'm fairly conversant in it.  
Quoted:
Quoted:
Great thread...I need to do this. I also am getting around to doing inventory with other items including GHB's, BOB's, vehicle kits, etc. I have done quite extensive work with Excel and Access. IMO Access can do anything (and much, much, much, more than Excel). Excel is more user friendly to setup, easier to make small changes, but eventually you'll have 10+ "sheets" and making a small change such as a change in a state sales tax will have to be replicated 10 times or more. Whereas, Access you can set it up once and if properly setup be expanded on rather easily. Basically I think Excel is good for small projects, but at some point you should really bite the bullet and learn Access and some MS Basic. Learn about how to properly structure databases (allowing for future changes), then setup some sweet forms and reports. Heck you could even setup Access to email you when a supply needs to be used/thrown away. The possibilities are endless. Access for the win! um no. if you set up excel properly from the beginning, using your example, a small change will not have to be replicated 10 times. you make the change once and it will populate the spreadsheet w/ the change. |