date add - Excel 2

[SQLScholar]

Hey,

Can someone help me??

I have a colomn with dates in them and a colomn with a number between 1 and 5. I need to get a formula to create another field which has the date, with the number (1-5) added on as years. If there is nothing in either field then put error.

Its probably simple.... but so i am at times.

TIA

Dan ----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[mwolf00]

dateVal = cells(1,1)
numVal = cells(1,2)

if isDate(dateVal) and isNumeric(numVaL) then
cells(1,3) = dateAdd(yyyy,numval,dateval)
else
cells(1,3) = "Error"
end if -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[RobBroekhuis]

Try this:

=IF(OR(A1=0,B1=0),&quot;error&quot;,DATEVALUE(MONTH(A1)&&quot;/&quot;&DAY(A1)&&quot;/&quot;&YEAR(A1)+B1))

where A1 has your date and B1 the year increment.
Rob

 

[SQLScholar]

Thanks guys, i went with the second option becuase it was easier to do it in formula then code.

One point tho (for future reference) i had to change the order to DAY MONTH YEAR rather then the other way round. Presumably due to regional settings.

Thanks agian STARS!

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!