Trying to use this formula "IF( G4=x, (DATE(YEAR(F4)+1,MONTH(F4),DAY(F4)), IF( H4=x, (DATE(YEAR(F4)+1.5,MONTH(F4),DAY(F4)), IF(I4=x,( DATE(YEAR(F4)+2,MONTH(F4),DAY(F4)))" to populate a field with a date from another cell after adding time to the date corresponding to a heading 1 year 1.5 year or 2 year, whichever has "x" under it. Can't seem to get it working. Anyone able to assist?
Last edited by youngjc345; 07-12-2015 at 12:02 PM.
If x is text then it should be in double quotes ... "x"
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
You don't need parens in front of date. Always be sure string references are in quotes. Clean up your long formula with Alt+Enter.
Please Login or Register to view this content.
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
Valuable point but still didn't fix the error of "Parenthesis missing"
Got rid of the error! now it says False regardless of any data inputs.
Does G4 or H4 have an x in it? Is F4 a date?
I'm also unclear on the difference between the 2nd and third IF statements. They both have the same test, therefore the 3rd one will never occur.
The third should be I4. Apologies. F4 is a date. Find image attached for more details. Thanks!
Screen Shot 2015-07-10 at 8.16.06 PM.png
Why do you have two tests for H4="x"? What's Year(F4)+1.5 supposed to do?
Please Login or Register to view this content.
alternatively, use:
Please Login or Register to view this content.
Last edited by protonLeah; 07-11-2015 at 02:04 PM.
Ben Van Johnson
Hey proton thanks for your response.
The duplicate H4 is an error that is supposed to be I4. The =DATE(YEAR(F4)+CHOOSE(MATCH("x",G4:I4,0),1,1.5,2),MONTH(F4),DAY(F4)) formula returns #N/A error.
With a valid date (TODAY()) in F4 the formula works OK but how can you add 1.5 to a year: the year changes by 1 (as per G4).
12, 18 & 24 are months to be added to F4 wrt G4:I4Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks