Can someone help with a formula to calculate dates?

Can someone help with a formula to calculate ending dates?

For example, I have a beginning date of 01/01/2006 and need to determine an
ending date. I need to figure an input variable that has been selected - 12
months
or 15 months. ie. if beginning date is 01/01/2006 - it needs to calculate
12 months from that date for an ending date of 12/31/2006.

Is this a table and vlookup situation or a formula?

On Mon, 8 Aug 2005 09:56:08 -0700, "SharonP."
<[email protected]> wrote:

>Can someone help with a formula to calculate ending dates?
>
>For example, I have a beginning date of 01/01/2006 and need to determine an
>ending date. I need to figure an input variable that has been selected - 12
>months
>or 15 months. ie. if beginning date is 01/01/2006 - it needs to calculate
>12 months from that date for an ending date of 12/31/2006.
>
>Is this a table and vlookup situation or a formula?

=EDATE(Start_date, Num_Months)


--ron

Thank you, I've tried this and it works great. One more question,
is there a way to do Num_Months minus 1 day?

"Ron Rosenfeld" wrote:

> On Mon, 8 Aug 2005 09:56:08 -0700, "SharonP."
> <[email protected]> wrote:
>
> >Can someone help with a formula to calculate ending dates?
> >
> >For example, I have a beginning date of 01/01/2006 and need to determine an
> >ending date. I need to figure an input variable that has been selected - 12
> >months
> >or 15 months. ie. if beginning date is 01/01/2006 - it needs to calculate
> >12 months from that date for an ending date of 12/31/2006.
> >
> >Is this a table and vlookup situation or a formula?
>
> =EDATE(Start_date, Num_Months)
>
>
> --ron
>

=EDATE(Start_date, Num_Months)-1

If I understood correctly.

SharonP. wrote:
>
> Thank you, I've tried this and it works great. One more question,
> is there a way to do Num_Months minus 1 day?
>
> "Ron Rosenfeld" wrote:
>
> > On Mon, 8 Aug 2005 09:56:08 -0700, "SharonP."
> > <[email protected]> wrote:
> >
> > >Can someone help with a formula to calculate ending dates?
> > >
> > >For example, I have a beginning date of 01/01/2006 and need to determine an
> > >ending date. I need to figure an input variable that has been selected - 12
> > >months
> > >or 15 months. ie. if beginning date is 01/01/2006 - it needs to calculate
> > >12 months from that date for an ending date of 12/31/2006.
> > >
> > >Is this a table and vlookup situation or a formula?
> >
> > =EDATE(Start_date, Num_Months)
> >
> >
> > --ron
> >

--

Dave Peterson

Thank you, how does it know that the -1 refers to days?

"Dave Peterson" wrote:

> =EDATE(Start_date, Num_Months)-1
>
> If I understood correctly.
>
> SharonP. wrote:
> >
> > Thank you, I've tried this and it works great. One more question,
> > is there a way to do Num_Months minus 1 day?
> >
> > "Ron Rosenfeld" wrote:
> >
> > > On Mon, 8 Aug 2005 09:56:08 -0700, "SharonP."
> > > <[email protected]> wrote:
> > >
> > > >Can someone help with a formula to calculate ending dates?
> > > >
> > > >For example, I have a beginning date of 01/01/2006 and need to determine an
> > > >ending date. I need to figure an input variable that has been selected - 12
> > > >months
> > > >or 15 months. ie. if beginning date is 01/01/2006 - it needs to calculate
> > > >12 months from that date for an ending date of 12/31/2006.
> > > >
> > > >Is this a table and vlookup situation or a formula?
> > >
> > > =EDATE(Start_date, Num_Months)
> > >
> > >
> > > --ron
> > >
>
> --
>
> Dave Peterson
>

Dates are just numbers to excel.

If you put today's date in a cell and format that cell as General, you'll see a
number like: 38572 (for Aug 8, 2005).

Excel just counts the number of days from a base date (12/31/1899 for most
windows users).

So when you subtract 1, you're subtracting one day.

In fact, time is a fraction of 1. 1 hour is equal to 1/24. 12 hours is 12/24
(or .5).

You can read lots more about how excel treats times (and dates) at Chip
Pearson's site:
http://www.cpearson.com/excel/datetime.htm



SharonP. wrote:
>
> Thank you, how does it know that the -1 refers to days?
>
> "Dave Peterson" wrote:
>
> > =EDATE(Start_date, Num_Months)-1
> >
> > If I understood correctly.
> >
> > SharonP. wrote:
> > >
> > > Thank you, I've tried this and it works great. One more question,
> > > is there a way to do Num_Months minus 1 day?
> > >
> > > "Ron Rosenfeld" wrote:
> > >
> > > > On Mon, 8 Aug 2005 09:56:08 -0700, "SharonP."
> > > > <[email protected]> wrote:
> > > >
> > > > >Can someone help with a formula to calculate ending dates?
> > > > >
> > > > >For example, I have a beginning date of 01/01/2006 and need to determine an
> > > > >ending date. I need to figure an input variable that has been selected - 12
> > > > >months
> > > > >or 15 months. ie. if beginning date is 01/01/2006 - it needs to calculate
> > > > >12 months from that date for an ending date of 12/31/2006.
> > > > >
> > > > >Is this a table and vlookup situation or a formula?
> > > >
> > > > =EDATE(Start_date, Num_Months)
> > > >
> > > >
> > > > --ron
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Originally Posted by Ron Rosenfeld

=EDATE(Start_date, Num_Months)

--ron

I'm getting a #NAME? error with this.

Is this correct?
=EDATE(8/8/2005,12)

Thanx.

The EDATE function is part of the Analysis Tool Pak add-in. Go to
the Tools menu, choose Add-Ins, then choose "Analysis Tool Pak"
from the list.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Ltat42a" <[email protected]>
wrote in message
news:[email protected]...
>
> Ron Rosenfeld Wrote:
>>
>>
>> =EDATE(Start_date, Num_Months)
>>
>> --ron
>
>
> I'm getting a #NAME? error with this.
>
> Is this correct?
> =EDATE(8/8/2005,12)
>
> Thanx.
>
>
> --
> Ltat42a
> ------------------------------------------------------------------------
> Ltat42a's Profile:
> http://www.excelforum.com/member.php...o&userid=24735
> View this thread:
> http://www.excelforum.com/showthread...hreadid=393939
>

On Mon, 8 Aug 2005 16:19:10 -0500, Ltat42a
<[email protected]> wrote:

>
>Ron Rosenfeld Wrote:
>>
>>
>> =EDATE(Start_date, Num_Months)
>>
>> --ron
>
>
>I'm getting a #NAME? error with this.

From HELP for EDATE:

If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.



>
>Is this correct?
>=EDATE(8/8/2005,12)

NO.

=EDATE("8/8/2005",12)

or, less ambiguous:

=EDATE(DATE(2005,8,8),12)


or put the date in some cell and use the cell reference:

A1:= 8/8/2005
B1:= =EDATE(A1,12)



--ron

And you'll want to make sure that first parm is a date:

=EDATE(date(2005,8,8),12)


Ltat42a wrote:
>
> Ron Rosenfeld Wrote:
> >
> >
> > =EDATE(Start_date, Num_Months)
> >
> > --ron
>
> I'm getting a #NAME? error with this.
>
> Is this correct?
> =EDATE(8/8/2005,12)
>
> Thanx.
>
> --
> Ltat42a
> ------------------------------------------------------------------------
> Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735
> View this thread: http://www.excelforum.com/showthread...hreadid=393939

--

Dave Peterson

Can you combine the edate function w/ a vlookup? For example I want to take
a date and add XX months but it gets a little complicated in that the XX is
actually a choice in a combo box from 12-15. The combo box links to cell D45
and the value of 12 is 1 since it's the first in the list
So, I created a lookup table and want to use that if that makes sense.

Lookup Month Table
1 12
2 13
3 14
4 15

Right now my formula is - =EDATE(Data!S2, D45) but D45 is 1 instead of 12.

Select number of months in renewal period Combo Box (choose from 12 - 15)
Results to 1 in the linked cell.



"Dave Peterson" wrote:

> Dates are just numbers to excel.
>
> If you put today's date in a cell and format that cell as General, you'll see a
> number like: 38572 (for Aug 8, 2005).
>
> Excel just counts the number of days from a base date (12/31/1899 for most
> windows users).
>
> So when you subtract 1, you're subtracting one day.
>
> In fact, time is a fraction of 1. 1 hour is equal to 1/24. 12 hours is 12/24
> (or .5).
>
> You can read lots more about how excel treats times (and dates) at Chip
> Pearson's site:
> http://www.cpearson.com/excel/datetime.htm
>
>
>
> SharonP. wrote:
> >
> > Thank you, how does it know that the -1 refers to days?
> >
> > "Dave Peterson" wrote:
> >
> > > =EDATE(Start_date, Num_Months)-1
> > >
> > > If I understood correctly.
> > >
> > > SharonP. wrote:
> > > >
> > > > Thank you, I've tried this and it works great. One more question,
> > > > is there a way to do Num_Months minus 1 day?
> > > >
> > > > "Ron Rosenfeld" wrote:
> > > >
> > > > > On Mon, 8 Aug 2005 09:56:08 -0700, "SharonP."
> > > > > <[email protected]> wrote:
> > > > >
> > > > > >Can someone help with a formula to calculate ending dates?
> > > > > >
> > > > > >For example, I have a beginning date of 01/01/2006 and need to determine an
> > > > > >ending date. I need to figure an input variable that has been selected - 12
> > > > > >months
> > > > > >or 15 months. ie. if beginning date is 01/01/2006 - it needs to calculate
> > > > > >12 months from that date for an ending date of 12/31/2006.
> > > > > >
> > > > > >Is this a table and vlookup situation or a formula?
> > > > >
> > > > > =EDATE(Start_date, Num_Months)
> > > > >
> > > > >
> > > > > --ron
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

=edate(data!s2,vlookup(d45,sheet3!a:b,2,false))

(or something like that--depending on where that table is.)

Or maybe:
=if(d45="","",edate(data!s2,vlookup(d45,sheet3!a:b,2,false)))

just in case that dropdown (from the forms toolbar) hasn't been used.



SharonP. wrote:
>
> Can you combine the edate function w/ a vlookup? For example I want to take
> a date and add XX months but it gets a little complicated in that the XX is
> actually a choice in a combo box from 12-15. The combo box links to cell D45
> and the value of 12 is 1 since it's the first in the list
> So, I created a lookup table and want to use that if that makes sense.
>
> Lookup Month Table
> 1 12
> 2 13
> 3 14
> 4 15
>
> Right now my formula is - =EDATE(Data!S2, D45) but D45 is 1 instead of 12.
>
> Select number of months in renewal period Combo Box (choose from 12 - 15)
> Results to 1 in the linked cell.
>
> "Dave Peterson" wrote:
>
> > Dates are just numbers to excel.
> >
> > If you put today's date in a cell and format that cell as General, you'll see a
> > number like: 38572 (for Aug 8, 2005).
> >
> > Excel just counts the number of days from a base date (12/31/1899 for most
> > windows users).
> >
> > So when you subtract 1, you're subtracting one day.
> >
> > In fact, time is a fraction of 1. 1 hour is equal to 1/24. 12 hours is 12/24
> > (or .5).
> >
> > You can read lots more about how excel treats times (and dates) at Chip
> > Pearson's site:
> > http://www.cpearson.com/excel/datetime.htm
> >
> >
> >
> > SharonP. wrote:
> > >
> > > Thank you, how does it know that the -1 refers to days?
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > =EDATE(Start_date, Num_Months)-1
> > > >
> > > > If I understood correctly.
> > > >
> > > > SharonP. wrote:
> > > > >
> > > > > Thank you, I've tried this and it works great. One more question,
> > > > > is there a way to do Num_Months minus 1 day?
> > > > >
> > > > > "Ron Rosenfeld" wrote:
> > > > >
> > > > > > On Mon, 8 Aug 2005 09:56:08 -0700, "SharonP."
> > > > > > <[email protected]> wrote:
> > > > > >
> > > > > > >Can someone help with a formula to calculate ending dates?
> > > > > > >
> > > > > > >For example, I have a beginning date of 01/01/2006 and need to determine an
> > > > > > >ending date. I need to figure an input variable that has been selected - 12
> > > > > > >months
> > > > > > >or 15 months. ie. if beginning date is 01/01/2006 - it needs to calculate
> > > > > > >12 months from that date for an ending date of 12/31/2006.
> > > > > > >
> > > > > > >Is this a table and vlookup situation or a formula?
> > > > > >
> > > > > > =EDATE(Start_date, Num_Months)
> > > > > >
> > > > > >
> > > > > > --ron
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson