How to get the Excel to calculate the exact date from a given date

[Phil]

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.

 

[Ron Coderre]

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

 

[Pete_UK]

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

 

[Morrigan]

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]

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 an
duration is 1 month. Is expiration date going to be Feb 30 or March
or something else

 

[Phil]

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

 

[Phil]

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?

 

[Ron Coderre]

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]

Hi Morrigan,

Please see my responses (preceded with Phil below.

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]

Hi Morrigan,

Please see my responses (preceded with Phil below.



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 t
your
question.

Is expiration date going to be Feb 30 or March 1

Phil: I'm not sure I'm following you here. Can you please expand o
this,
if needed, based on my response above?>

I used 1 month just as an example to demonstrate what should happe
with some months have 31 days and some have 30 days or less. Since Fe
does not have 31st, if you sign a contract on Jan 31st and the duratio
ends in the month with less than 31 days, what do you want th
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 fo
the above situation assuming the correct output would be Feb 28

 

[Phil]

Oh, I see what you mean. Alright, I will give that last formula you posted a
shot.