Assistance with 'IF' formula in Excel

  oo7juk 16:14 30 Oct 07
Locked

Hi,

Can someone assist with following formuala please.

I have 3 cells A1, B1 & C1

F3 is titled Date
J3 is titled Time limit (contains drop-down)
drop down items - 3 months, 6 months, 9 months, 12 months
K3 is titled Expires

When I select an item from J3 e.g. 3 months I want to populate K3 with a date 3 months in advance. So if I enter 30.10.07 in F3 and select 3 months from J3 then K1 populates with 30.01.08.

I Know a formula to use to create future dates, but can't get the 'time limit' feature to work.

This is the formula I have been working on -

=IF(ISTEXT(F3),"",IF(J3 4,"DATE(YEAR(F3),MONTH(F3)+3,DAY(F3)),""))",IF(J3 7,"DATE(YEAR(F3),MONTH(F3)+6,DAY(F3)),""))",IF(J3 13,"DATE(YEAR(F3),MONTH(F3)+12,DAY(F3)),""))"))))

Many thanks.

  silverous 16:23 30 Oct 07

Errrm...how do A1 B1 & C1 come into it ?!

  silverous 16:25 30 Oct 07

What's wrong with what you've posted - you don't say?

I'm assuming where you've got IF(J3 4

you mean IF(J3=4

etc.?

  silverous 16:27 30 Oct 07

You also haven't said what the values in the drop down are - i.e. are they actually values "3,6,9,12" or do they contain "3 months" ?

I'm not sure why your IF has the J3 4 then DATE in quotes.

Give a bit more info and I can help.

  silverous 16:31 30 Oct 07

Also not sure why you have the ISTEXT on F3 - if you put 30.01.08 into a cell it will think ISTEXT is the case unless it is formatted as a date I believe.

  silverous 16:35 30 Oct 07

If it helps, if I assume that you have the value "3 months" in the drop down (and not the value 3) then the following will rip out the number of months from that cell:

LEFT(J3,FIND(" ",J3,1)

which you can then add to the date in F3 e.g.:

=DATE(YEAR(F3),MONTH(F3)+LEFT(J3,FIND(" ",J3,1)),DAY(F3))

  VoG II 16:38 30 Oct 07

Or, if F3 contains a value recognised by Excel as a date (not a string)

=DATE(YEAR(F3),MONTH(F3)+J3,DAY(F3))

  bstb3 16:51 30 Oct 07

You can try this one too:

=IF(ISTEXT(F3),"",IF(J3="3 Months",DATE(YEAR(F3),MONTH(F3)+3,DAY(F3)),(IF(J3="6 Months",DATE(YEAR(F3),MONTH(F3)+6,DAY(F3)),(IF(J3="12 Months",DATE(YEAR(F3),MONTH(F3)+12,DAY(F3)),IF(J3="9 Months",DATE(YEAR(F3),MONTH(F3)+9,DAY(F3)),"Time Error in J3")))))))

If the values in the drop down cell list in J3 arent 3 Months etc then change the values in the comments in the above formula to suit.

Much prefer Silverous's solution though, far neater.

I presume the ISTEXT was to check if there was any entry in the F3 cell and return a blank if not?

  silverous 17:13 30 Oct 07

I thought that but found that it didn't work (the ISTEXT) - perhaps it is my formatting.

  oo7juk 23:01 30 Oct 07

Hi,

Many thanks for all the advice. Silverous A1, B1 etc was a mistake. When I started to write question I thought I would use these examples for ease of reference, my apologies. The ISTEXT part was basically taken from a formula I used in another workbook, tried to change to suit what I was trying to do. Your formula - =DATE(YEAR(F3),MONTH(F3)+LEFT(J3,FIND(" ",J3,1)),DAY(F3)) done what I was wanting and the drop down items do contain numeric and text.

MAny thanks again for all your help.

  silverous 09:36 31 Oct 07

Great stuff, can you tick as resolved?

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…