Need Formula for Calculating Expiry Date of Items

[sweetfriend9]

I need formula for calculating the expiry dates as mentioned in the attached sheet .

[AliGW]

So, what is the calculation that you wish to perform? How long after the best before date do items expire?

[sweetfriend9]

pls see the attachment ( manual calculation done )

[AliGW]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[sweetfriend9]

will take care of the multiple posts in future , thanks for the reminder

[AliGW]

will take care of the multiple posts in future , thanks for the reminder

You need to take care of it this time, too, please. If your problem has been solved elsewhere, then you need to say so and mark the thread as solved so that nobody ends up wasting their time.

[FDibbins]

Please provide the link to your x-post. The other web site will also require you to do this

[sweetfriend9]

My query isnt solved yet , pls help

[AliGW]

My query isnt solved yet , pls help

You have not yet provided a link to posts that you have made on other forums. When you have done that, one of us will help.

[sweetfriend9]

If you are happy helping me then its okay , i havent posted it anywhere

[AliGW]

If you are happy helping me then its okay , i havent posted it anywhere

You have posted exactly the same query on another forum under the name of warsisalman. Please provide a link to that thread.

[sweetfriend9]

ask the admin to block me here

[AliGW]

ask the admin to block me here

Why not just provide the link requested? If you do that, you will get help.

[Aardigspook]

@sweetfriend9
I can give you a possible solution, but please comply with Rule 8 first - just post a link to your thread on the other site. Once you've done that, you'll find folk are much more willing to help you.

[sweetfriend9]

help me or not take this one , i jumped onto the other forum coz i was not getting the solution here ... Thanks anyway

http://www.mrexcel.com/forum/excel-q...iry-dates.html

[AliGW]

Thank you for the link. It was under half an hour between posting here and on the other forum. You have to understand that replies will not necessarily be instant. There is not a problem asking elsewhere as long as you give a link so that people here know. Hopefully Ardigspook will now offer you help.

[Aardigspook]

As I understand your question, you want to have the expiry date equal to either: (1) the best before date if that's given or (2) the date of purchase/mfg plus the length of time given in the best-before-date column.
This formula (in cell E2) will give you a good approximation of the expiry date:

=IF(ISNUMBER(D2),D2,IF(RIGHT(D2,6)="Months",C2+((365/12)*LEFT(D2,LEN(D2)-(LEN(D2)-FIND(" ",D2)))),IF(RIGHT(D2,5)="Years",C2+(365*LEFT(D2,LEN(D2)-(LEN(D2)-FIND(" ",D2)))),"nothing found")))

It first checks if D2 is a number (Excel treats a date as a number). If so, it returns D2. That is, if D2 is a date, E2 = D2.
Then it checks to see if D2 ends with 'Months'. If so, it finds the number of months (that's the LEFT(D2,LEN(D2)-(LEN(D2)-FIND(" ",D2))) bit), multiplies that by 365/12 (which gives an approximation of the number of days in a month), then adds that to the date in C2.
Then it checks if D2 ends with 'Years'. If so, it finds the number of years and multiplies that by 365, then adds that to the date in C2.
If no date is found in C2, 'nothing found' is returned - you can replace this with anything you want - use "" to return a blank.

As you'll see, the date isn't 100% accurate for the '6 Months' and '1.5 Years'. It should be possible to make it accurate, but the formula will be a lot more complicated as it would need to take account of the number of months left in the year of the date in column C. I'd rather not start digging into that unless you really need it.

Does the formula above work well enough for you?

[Phuocam]

Or try this in cell E2 ...

=IF(D2="","",IF(N(D2)>C2,D2,EDATE(C2,IF(COUNTIF(D2,"*Months*"),--SUBSTITUTE(D2," Months",""),12*SUBSTITUTE(D2," Years","")))))

Copy down.

[sweetfriend9]

Thanks it worked , only slight change if possible , it's not working for Text = Month or Year

[Aardigspook]

@Phuocam: much better than mine - I've had to create so many files for 2003 users that I always forget about EDATE!

@sweetfriend9: Phuocam's formula amended to cope with Month/Months/Year/Years:

=IF(D5="","",IF(N(D5)>C5,D5,EDATE(C5,IF(COUNTIF(D5,"*Months*"),--SUBSTITUTE(D5," Months",""),IF(COUNTIF(D5,"*Month*"),--SUBSTITUTE(D5," Month",""),IF(COUNTIF(D5,"*Years*"),--SUBSTITUTE(D5," Years",""),12*SUBSTITUTE(D5," Year","")))))))

[Phuocam]

Another way ...

=IF(D2="","",IF(N(D2)>C2,D2,EDATE(C2,LOOKUP(10^10,--LEFT(D2,{1,2,3,4,5,6,7,8}))*IF(COUNTIF(D2,"*Month*"),1,12))))

[sweetfriend9]

Thanks ... You all are like ... what i want to be ...

[Phuocam]

You are welcome!