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


"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.
>
>

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

Since the input is month, I am going to assume the output to be the end of the month. Try the following:

I3 = DATE(YEAR(G3)+QUOTIENT(H3,12),MONTH(G3)+MOD(H3,12),IF(DAY(G3)>DAY(EOMONTH(DATE(YEAR(G3)+QUOTIENT(H3,12),MONTH(G3)+MOD(H3,12),1),0)),DAY(EOMONTH(DATE(YEAR(G3)+QUOTIENT(H3,12),MONTH(G3)+MOD(H3,12),1),0)),DAY(G3)))

[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



"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.
> >
> >

[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?

"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
>
>

[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" 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.
> > >
> > >

[Phil]

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
>
>

[Morrigan]

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 to be Feb 28.

[Phil]

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
>
>