Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hello,
I have five columns shown below, with sample values after the colon (Col F) Today's Date: 04-28-06 {this will be using the =TODAY() function} (Col G) Contract Date Signed: 03-28-06 (Col H) Duration (Months): 12 (Col I) Expiration Date: 03-28-07* (Col J) Days Remaining in Contract: 334* * these are hypothetical, they are not correct If the user puts 12 in column H, for the duration, the result will be 03-28-07. But what if someone puts something other than 12, like 8, 6, 7, or 13? I need someway to be able to get Excel to tell me the EXACT day that the contract will expire, AND how many days are left in the contract. BTW, the user will ALWAYS be entering whole numbers, not decimals, eg. 6.5 months. Thanks in advance for your responses. Phil. |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Here are 2 options:
For data in Row_2 1) I2: =EDATE(G2,H2) Note: the EDATE function is part of the Analysis ToolPak add-in. You might need to enable or install it. (<Tools<Add-ins...etc) 2) I2: =DATE(YEAR(G2),MONTH(G2)+H2,DAY(G2)) For days remaining: J2: I2-F2 (formatted as a number) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Phil" wrote: Hello, I have five columns shown below, with sample values after the colon (Col F) Today's Date: 04-28-06 {this will be using the =TODAY() function} (Col G) Contract Date Signed: 03-28-06 (Col H) Duration (Months): 12 (Col I) Expiration Date: 03-28-07* (Col J) Days Remaining in Contract: 334* * these are hypothetical, they are not correct If the user puts 12 in column H, for the duration, the result will be 03-28-07. But what if someone puts something other than 12, like 8, 6, 7, or 13? I need someway to be able to get Excel to tell me the EXACT day that the contract will expire, AND how many days are left in the contract. BTW, the user will ALWAYS be entering whole numbers, not decimals, eg. 6.5 months. Thanks in advance for your responses. Phil. |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
How many days are there in a month, though? If it is 8 months, do you
want it to terminate exactly on the same day eight months hence? Pete |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]() Phil Wrote: Hello, I have five columns shown below, with sample values after the colon (Col F) Today's Date: 04-28-06 {this will be using the =TODAY() function} (Col G) Contract Date Signed: 03-28-06 (Col H) Duration (Months): 12 (Col I) Expiration Date: 03-28-07* (Col J) Days Remaining in Contract: 334* * these are hypothetical, they are not correct If the user puts 12 in column H, for the duration, the result will be 03-28-07. But what if someone puts something other than 12, like 8, 6, 7, or 13? I need someway to be able to get Excel to tell me the EXACT day that the contract will expire, AND how many days are left in the contract. BTW, the user will ALWAYS be entering whole numbers, not decimals, eg. 6.5 months. Thanks in advance for your responses. Phil. Try this I3 = DATE(YEAR(G3)+QUOTIENT(H3,12),MONTH(G3)+MOD(H3,12) ,DAY(G3)) J3 = VALUE(I3)-VALUE(F3) -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=537229 |
#5
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]() Morrigan Wrote: Try this I3 = DATE(YEAR(G3)+QUOTIENT(H3,12),MONTH(G3)+MOD(H3,12) ,DAY(G3)) J3 = VALUE(I3)-VALUE(F3) Hmm....forgot to account for last day of the month. What do you want to show if you sign the contract on Jan 31 and duration is 1 month. Is expiration date going to be Feb 30 or March 1 or something else? -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=537229 |
#6
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hi Ron,
I went with Option 2, I tried that and it works GREAT! So that is what I will go with. With regard to Option 1, if I were to send the spreadsheet to someone else (like the client), they would have to be sure to have the Add-In loaded as well, right? And if they didn't what would happen? Would the dependent cell (on the Add-In) be blank, then? HOWEVER, I realized after sending my first post that there ARE going to be contracts that will be 18 AND 24 months, and that would put some of the expiration dates into 2008, which is a Leap year. What is(are) your opinion(s) on the Leap year issue? Thanks again in advance for your replies. Phil "Ron Coderre" wrote: Here are 2 options: For data in Row_2 1) I2: =EDATE(G2,H2) Note: the EDATE function is part of the Analysis ToolPak add-in. You might need to enable or install it. (<Tools<Add-ins...etc) 2) I2: =DATE(YEAR(G2),MONTH(G2)+H2,DAY(G2)) For days remaining: J2: I2-F2 (formatted as a number) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Phil" wrote: Hello, I have five columns shown below, with sample values after the colon (Col F) Today's Date: 04-28-06 {this will be using the =TODAY() function} (Col G) Contract Date Signed: 03-28-06 (Col H) Duration (Months): 12 (Col I) Expiration Date: 03-28-07* (Col J) Days Remaining in Contract: 334* * these are hypothetical, they are not correct If the user puts 12 in column H, for the duration, the result will be 03-28-07. But what if someone puts something other than 12, like 8, 6, 7, or 13? I need someway to be able to get Excel to tell me the EXACT day that the contract will expire, AND how many days are left in the contract. BTW, the user will ALWAYS be entering whole numbers, not decimals, eg. 6.5 months. Thanks in advance for your responses. Phil. |
#7
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hi Pete_UK:
I would want it to terminate NOT by the count of the months (whole number), but by the ACTUAL amount of days that would be associated with the 8 month period. Does that answer your question? "Pete_UK" wrote: How many days are there in a month, though? If it is 8 months, do you want it to terminate exactly on the same day eight months hence? Pete |
#8
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Since not all months have 30 days, there are more issues than just dealing
with leapyears. For example: Contract Start Date: 02/28/2006 Does that mean it ends on the 28th of the last month? or the end of the month? Same issue for months ending on the 30th. Hopefully, your contracts address the situation by specifying an expiration date. If your spreadsheet is meant, in part, to act as a reminder you might want to flag contracts approaching the calculated expiration date. I hope that helps. *********** Regards, Ron XL2002, WinXP-Pro "Phil" wrote: Hi Ron, I went with Option 2, I tried that and it works GREAT! So that is what I will go with. With regard to Option 1, if I were to send the spreadsheet to someone else (like the client), they would have to be sure to have the Add-In loaded as well, right? And if they didn't what would happen? Would the dependent cell (on the Add-In) be blank, then? HOWEVER, I realized after sending my first post that there ARE going to be contracts that will be 18 AND 24 months, and that would put some of the expiration dates into 2008, which is a Leap year. What is(are) your opinion(s) on the Leap year issue? Thanks again in advance for your replies. Phil "Ron Coderre" wrote: Here are 2 options: For data in Row_2 1) I2: =EDATE(G2,H2) Note: the EDATE function is part of the Analysis ToolPak add-in. You might need to enable or install it. (<Tools<Add-ins...etc) 2) I2: =DATE(YEAR(G2),MONTH(G2)+H2,DAY(G2)) For days remaining: J2: I2-F2 (formatted as a number) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Phil" wrote: Hello, I have five columns shown below, with sample values after the colon (Col F) Today's Date: 04-28-06 {this will be using the =TODAY() function} (Col G) Contract Date Signed: 03-28-06 (Col H) Duration (Months): 12 (Col I) Expiration Date: 03-28-07* (Col J) Days Remaining in Contract: 334* * these are hypothetical, they are not correct If the user puts 12 in column H, for the duration, the result will be 03-28-07. But what if someone puts something other than 12, like 8, 6, 7, or 13? I need someway to be able to get Excel to tell me the EXACT day that the contract will expire, AND how many days are left in the contract. BTW, the user will ALWAYS be entering whole numbers, not decimals, eg. 6.5 months. Thanks in advance for your responses. Phil. |
#9
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hi Morrigan,
Please see my responses (preceded with Phil:) below. "Morrigan" wrote: Morrigan Wrote: Try this I3 = DATE(YEAR(G3)+QUOTIENT(H3,12),MONTH(G3)+MOD(H3,12) ,DAY(G3)) J3 = VALUE(I3)-VALUE(F3) Hmm....forgot to account for last day of the month. What do you want to show if you sign the contract on Jan 31 and duration is 1 month. Phil: I won't have any instances that will have a 1 month contract. The lowest contracts that we will have is 3 months, if that is relevant to your question. Is expiration date going to be Feb 30 or March 1 or something else? Phil: I'm not sure I'm following you here. Can you please expand on this, if needed, based on my response above? -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=537229 |
#10
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]() Phil Wrote: Hi Morrigan, Please see my responses (preceded with Phil:) below. "Morrigan" wrote: Morrigan Wrote: Try this I3 = DATE(YEAR(G3)+QUOTIENT(H3,12),MONTH(G3)+MOD(H3,12) ,DAY(G3)) J3 = VALUE(I3)-VALUE(F3) Hmm....forgot to account for last day of the month. What do you want to show if you sign the contract on Jan 31 and duration is 1 month. Phil: I won't have any instances that will have a 1 month contract. The lowest contracts that we will have is 3 months, if that is relevant to your question. Is expiration date going to be Feb 30 or March 1 or something else? Phil: I'm not sure I'm following you here. Can you please expand on this, if needed, based on my response above? -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=537229 I used 1 month just as an example to demonstrate what should happen with some months have 31 days and some have 30 days or less. Since Feb does not have 31st, if you sign a contract on Jan 31st and the duration ends in the month with less than 31 days, what do you want the expiration date to be? Should it be Feb 28 or March 1 or neither? In my second post, I modified the equation so that it will account for the above situation assuming the correct output would be Feb 28. -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=537229 |
#11
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Oh, I see what you mean. Alright, I will give that last formula you posted a
shot. "Morrigan" wrote: Phil Wrote: Hi Morrigan, Please see my responses (preceded with Phil:) below. "Morrigan" wrote: Morrigan Wrote: Try this I3 = DATE(YEAR(G3)+QUOTIENT(H3,12),MONTH(G3)+MOD(H3,12) ,DAY(G3)) J3 = VALUE(I3)-VALUE(F3) Hmm....forgot to account for last day of the month. What do you want to show if you sign the contract on Jan 31 and duration is 1 month. Phil: I won't have any instances that will have a 1 month contract. The lowest contracts that we will have is 3 months, if that is relevant to your question. Is expiration date going to be Feb 30 or March 1 or something else? Phil: I'm not sure I'm following you here. Can you please expand on this, if needed, based on my response above? -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=537229 I used 1 month just as an example to demonstrate what should happen with some months have 31 days and some have 30 days or less. Since Feb does not have 31st, if you sign a contract on Jan 31st and the duration ends in the month with less than 31 days, what do you want the expiration date to be? Should it be Feb 28 or March 1 or neither? In my second post, I modified the equation so that it will account for the above situation assuming the correct output would be Feb 28. -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=537229 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Calendar Control in Excel 2000 can't display date in Excel 2003? | Excel Discussion (Misc queries) | |||
excel array formula | Excel Worksheet Functions | |||
Excel default date format | Excel Discussion (Misc queries) | |||
How do I calculate a future date in Excel? | Excel Worksheet Functions | |||
Excel enters date as a text format | Excel Discussion (Misc queries) |