Shelf life determined by Juian date.

[AmeliaRose]

I need to make a guideline for our shipping department that will allow them to look up, in reverse, what dates they can ship products from that have a limited shelf life.

The problem is that everything is labelled as a lot number - meaning when they see a product it will be numbered (for example) 6002. That date translates to January 2, 2016.

So if they looked at a product labelled as that the table would be able to tell them if the product has 66% or 80% of it's shelf life remaining. It's then divided into products that are either 6, 8, 12, or 24 months. We were able to do this until the new year switched over because the table won't subtract days if the Julian is 001, it will go into the negative instead of counting down from 365. I've attached the old table we were using but that's just to give you an idea of what I'm talking about.

I cannot figure out how to get it to calculate and was hoping someone could help?

[dflak]

What numbers would you like to see in column D?

[AmeliaRose]

The number in column D would the Julian date that it would be at if it were 66%, the F would be at 80%.

[JeteMc]

Try pasting the following formulas in D4 and F4 respectively:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Note: If you were to label C1 '6 mois' and E1 '6 mois' you could simply copy from D4 and paste to F4 etc.
Let me know if you have any questions.