Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Phil
 
Posts: n/a
Default How to get the Excel to calculate the exact date from a given date

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default How to get the Excel to calculate the exact date from a given date

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Pete_UK
 
Posts: n/a
Default How to get the Excel to calculate the exact date from a given date

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Morrigan
 
Posts: n/a
Default How to get the Excel to calculate the exact date from a given date


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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Morrigan
 
Posts: n/a
Default How to get the Excel to calculate the exact date from a given date


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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Phil
 
Posts: n/a
Default How to get the Excel to calculate the exact date from a given

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Phil
 
Posts: n/a
Default How to get the Excel to calculate the exact date from a given

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default How to get the Excel to calculate the exact date from a given

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Phil
 
Posts: n/a
Default How to get the Excel to calculate the exact date from a given

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Morrigan
 
Posts: n/a
Default How to get the Excel to calculate the exact date from a given date


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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Phil
 
Posts: n/a
Default How to get the Excel to calculate the exact date from a given

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calendar Control in Excel 2000 can't display date in Excel 2003? Lewis Excel Discussion (Misc queries) 0 April 21st 06 05:07 PM
excel array formula gordo Excel Worksheet Functions 14 October 18th 05 05:19 PM
Excel default date format mjk Excel Discussion (Misc queries) 1 August 16th 05 10:52 PM
How do I calculate a future date in Excel? macheath48 Excel Worksheet Functions 2 August 3rd 05 04:12 PM
Excel enters date as a text format Kane Excel Discussion (Misc queries) 3 March 22nd 05 10:20 PM


All times are GMT +1. The time now is 09:27 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"