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