Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced expiry date calculations
Hello!
I asked some questions earlier today in another thread regarding the calculation of expiry dates in Excel (the thread name is "Problem with "IF" function"), but I have run into a new issue that is so complex (to me, at least) that I chose to make a new thread about it. I have successfully made a worksheet that will give a warning when an item is about to expire. Here is an example: John Doe has a license that will expire on August 31st, 2009. On August 12th, 2009 he renews his license. This renewal lasts for 12 months plus the remainder of the last month, in this case until August 31st, 2010. I have been able to calculate this in Excel with relative ease. But here is the new challenge I'm facing: John Doe has a license that will expire on September 30th, 2009. He can renew this license at any time in the three months preceding expiry (i.e. from July 1st, 2009 to September 30th, 2009) and have the license renewed until September 30th, 2010. In other words the duration of the license will be 12 months from September 30th, 2009 plus any number of days up to three months before September 30th, 2009. I hope this explanation is understandable. If not, I'll try to explain it better. My question is: Can this calculation be done in Excel? Thanks in advance for any help! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced expiry date calculations
Let's say that the current expiration date is in A2, and renewal date is in A3:
New expiration date: =IF(A3<=A2,DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)),DATE (YEAR(A3)+1,MONTH(A3),DAY(A3))) If you're wanting to know how many days from renewal date to new expiration date: =IF(A3<=A2,DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)),DATE (YEAR(A3)+1,MONTH(A3),DAY(A3)))-A3 Note that you may need to format second formula as a number, as it may default to a date format. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Torfinn Brokke" wrote: Hello! I asked some questions earlier today in another thread regarding the calculation of expiry dates in Excel (the thread name is "Problem with "IF" function"), but I have run into a new issue that is so complex (to me, at least) that I chose to make a new thread about it. I have successfully made a worksheet that will give a warning when an item is about to expire. Here is an example: John Doe has a license that will expire on August 31st, 2009. On August 12th, 2009 he renews his license. This renewal lasts for 12 months plus the remainder of the last month, in this case until August 31st, 2010. I have been able to calculate this in Excel with relative ease. But here is the new challenge I'm facing: John Doe has a license that will expire on September 30th, 2009. He can renew this license at any time in the three months preceding expiry (i.e. from July 1st, 2009 to September 30th, 2009) and have the license renewed until September 30th, 2010. In other words the duration of the license will be 12 months from September 30th, 2009 plus any number of days up to three months before September 30th, 2009. I hope this explanation is understandable. If not, I'll try to explain it better. My question is: Can this calculation be done in Excel? Thanks in advance for any help! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced expiry date calculations
I'm a little confused by your "3 months prior" limitation. Do you mean if
John Doe tried to renew (extend) his license for another year four months prior to the expiration date, you wouldn't let him? With that said, you can set up an IF test to see if the date is within 3 months of the expiration date and simple add one year to the expiration date to get the new expiration date (not sure why you are trying to add remaining days in current license... a renewal for 1 year extends the expiration date by 1 year, no matter when it is implemented (within your 3 month restriction), right... or am I missing something in your question? To get the date a year from a given date, just use the DATE function something like this... Assuming E2 contains the old expiration date, put the formula for the new expiration date in, say, F2... =DATE(YEAR(E2)+1,MONTH(E2),DAY(E2)) -- Rick (MVP - Excel) "Torfinn Brokke" wrote in message ... Hello! I asked some questions earlier today in another thread regarding the calculation of expiry dates in Excel (the thread name is "Problem with "IF" function"), but I have run into a new issue that is so complex (to me, at least) that I chose to make a new thread about it. I have successfully made a worksheet that will give a warning when an item is about to expire. Here is an example: John Doe has a license that will expire on August 31st, 2009. On August 12th, 2009 he renews his license. This renewal lasts for 12 months plus the remainder of the last month, in this case until August 31st, 2010. I have been able to calculate this in Excel with relative ease. But here is the new challenge I'm facing: John Doe has a license that will expire on September 30th, 2009. He can renew this license at any time in the three months preceding expiry (i.e. from July 1st, 2009 to September 30th, 2009) and have the license renewed until September 30th, 2010. In other words the duration of the license will be 12 months from September 30th, 2009 plus any number of days up to three months before September 30th, 2009. I hope this explanation is understandable. If not, I'll try to explain it better. My question is: Can this calculation be done in Excel? Thanks in advance for any help! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced expiry date calculations
Hello, Rick!
Let me try to explain a little better: Let's say the license in question is valid until September 30th, and that each renewal is for one year. If the person in question was to renew his license on September 30th, 2009, the duration would be until September 30th, 2010 (i.e. 12 months). However, if renewed within the three months prior to September 30th, 2009 the 12 month renewal would still be until September 30th, 2010. That is, depending on when the license is renewed the actual duration would be anywhere from 12 to 15 months. Best regards, Torfinn "Rick Rothstein" wrote: I'm a little confused by your "3 months prior" limitation. Do you mean if John Doe tried to renew (extend) his license for another year four months prior to the expiration date, you wouldn't let him? With that said, you can set up an IF test to see if the date is within 3 months of the expiration date and simple add one year to the expiration date to get the new expiration date (not sure why you are trying to add remaining days in current license... a renewal for 1 year extends the expiration date by 1 year, no matter when it is implemented (within your 3 month restriction), right... or am I missing something in your question? To get the date a year from a given date, just use the DATE function something like this... Assuming E2 contains the old expiration date, put the formula for the new expiration date in, say, F2... =DATE(YEAR(E2)+1,MONTH(E2),DAY(E2)) -- Rick (MVP - Excel) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced expiry date calculations
Hello, Luke, and thanks for your reply!
Unfortunately, I'm getting an error message when I try to input your formula in Excel, and I'm not able to see exactly where the error is. Best regards, Torfinn "Luke M" wrote: Let's say that the current expiration date is in A2, and renewal date is in A3: New expiration date: =IF(A3<=A2,DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)),DATE (YEAR(A3)+1,MONTH(A3),DAY(A3))) If you're wanting to know how many days from renewal date to new expiration date: =IF(A3<=A2,DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)),DATE (YEAR(A3)+1,MONTH(A3),DAY(A3)))-A3 Note that you may need to format second formula as a number, as it may default to a date format. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced expiry date calculations
Are you sure you have dates (as numbers) in cells A2 & A3, or do you have
text that looks like dates? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Torfinn Brokke" wrote: Hello, Luke, and thanks for your reply! Unfortunately, I'm getting an error message when I try to input your formula in Excel, and I'm not able to see exactly where the error is. Best regards, Torfinn "Luke M" wrote: Let's say that the current expiration date is in A2, and renewal date is in A3: New expiration date: =IF(A3<=A2,DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)),DATE (YEAR(A3)+1,MONTH(A3),DAY(A3))) If you're wanting to know how many days from renewal date to new expiration date: =IF(A3<=A2,DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)),DATE (YEAR(A3)+1,MONTH(A3),DAY(A3)))-A3 Note that you may need to format second formula as a number, as it may default to a date format. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced expiry date calculations
Hello again, Luke!
I found the error: I had to replace all your commas with semicolons. I supposed this is a difference between the US and European versions of Excel? Anyway, that made your formula work just fine! However, I'm still not quite where I'd like to be. Would it be possible to make a formula to calculate this from only two inputs: Renewal date and time until expiry? Example: In C17 I have the renewal date (let's say 13.02.2009), and in D17 I have the validity period in months (in this case 12). Now, for the licenses that have a one year duration plus the remainder of the month of expiry, I calculate the expiry date in E17 by using =EOMONTH(C17;D17). What I would like to achieve is a system that just lets you put in one date, basically, and it will tell you when the license will expire, and in this more advanced case take into account the fact that the duration can be anywhere from 12 to 15 months depending on the renewal date. Is it possible to achieve this in one single operation, or do I need some sort of in-between calculation? (Also, the formula should take into account that the duration is always to the end of the month of expiry, like above.) Thanks for your great help so far! Best regards, Torfinn "Luke M" wrote: Are you sure you have dates (as numbers) in cells A2 & A3, or do you have text that looks like dates? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced expiry date calculations
Oops...
On closer examination I see that what I'm asking for here is impossible, since the formula would need to know the original expiry date at some point. I'm a bit at a loss how this could be done in the most user-friendly way. Do you have any suggestions? Best regards, Torfinn "Torfinn Brokke" wrote: Hello again, Luke! I found the error: I had to replace all your commas with semicolons. I supposed this is a difference between the US and European versions of Excel? Anyway, that made your formula work just fine! However, I'm still not quite where I'd like to be. Would it be possible to make a formula to calculate this from only two inputs: Renewal date and time until expiry? Example: In C17 I have the renewal date (let's say 13.02.2009), and in D17 I have the validity period in months (in this case 12). Now, for the licenses that have a one year duration plus the remainder of the month of expiry, I calculate the expiry date in E17 by using =EOMONTH(C17;D17). What I would like to achieve is a system that just lets you put in one date, basically, and it will tell you when the license will expire, and in this more advanced case take into account the fact that the duration can be anywhere from 12 to 15 months depending on the renewal date. Is it possible to achieve this in one single operation, or do I need some sort of in-between calculation? (Also, the formula should take into account that the duration is always to the end of the month of expiry, like above.) Thanks for your great help so far! Best regards, Torfinn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highlight expiry date | Setting up and Configuration of Excel | |||
VBA For Expiry date | Excel Discussion (Misc queries) | |||
Expiry date | Excel Worksheet Functions | |||
Date Expiry function | Excel Discussion (Misc queries) | |||
Date of expiry - warning? | Excel Worksheet Functions |