# Can someone help with a formula to calculate dates?

G

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

R

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

G

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

D

#### Dave Peterson

=EDATE(Start_date, Num_Months)-1

If I understood correctly.

G

#### Guest

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

D

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

L

#### Ltat42a

Ron said:
=EDATE(Start_date, Num_Months)

--ron

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

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

Thanx

C

#### Chip Pearson

The EDATE function is part of the Analysis Tool Pak add-in. Go to
from the list.

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

"Ltat42a" <[email protected]>
wrote in message

R

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

How?

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

D

#### Dave Peterson

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

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

G

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

D

#### 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. said:
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.