Can someone help with a formula to calculate dates?

[Guest]

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?

 

[Ron Rosenfeld]

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

 

[Guest]

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

 

[Dave Peterson]

=EDATE(Start_date, Num_Months)-1

If I understood correctly.

 

[Guest]

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

 

[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

 

[Ltat42a]

=EDATE(Start_date, Num_Months)

--ron

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

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

Thanx

 

[Chip Pearson]

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

 

[Ron Rosenfeld]

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

 

[Dave Peterson]

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

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

 

[Guest]

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]

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

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.