Hi, Can you help. I'm looking to insert this formula in my spread sheet.

=IF(D3="Monthly",EDATE(E3,1),IF(D3="Quarterly",EDATE(E3,3),IF(D3="Annual",EDATE(E3,12),IF(D3="Bi-Annual",EDATE(E3,6)," "))))

D3 = Period

Monthly or Quarterly or Annual or Bi-AnnualE3 is a date field

However, when there is no date in E3 I want the formula to give me blank.

Thanks in advance.

✔ Best Answer

Just in case you want to have some fun, this (shorter) formula seems to work also: =IF(E3>0,EDATE(E3,CHOOSE(MATCH(CODE(D3),{65,66,77,81}),12,6,1,3)),"")

It would be a fun and educational exercise for you to figure out how it works. Give it a try and then come on back if you need any assistance with breaking it down.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03

Not completely sure, but try this: =IF(OR(D3="",E3=""),"",IF(D3="Monthly",EDATE(E3,1),IF(D3="Quarterly",EDATE(E3,3),IF(D3="Annual",EDATE(E3,12),IF(D3="Bi-Annual",EDATE(E3,6),"")))))

First check to see if D3 or E3 is blank.

Is that what your looking for?

MIKE

Just add IF(E3="","",.....))))) as your first test. An IF function stops at the first True result.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

BTW...In your formula, you are using a "space" to return a blank cell. A space is an actual text character and that could cause problems in certain cases. It's better to use a null string, represented by "" (2 double quotes) to display an empty cell.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Thanks for the suggestions. I've used the following code now; =IF(E3="","",IF(D3="Monthly",EDATE(E3,1),IF(D3="Quarterly",EDATE(E3,3),IF(D3="Annual",EDATE(E3,12),IF(D3="Bi-Annual",EDATE(E3,6),"")))))

Just in case you want to have some fun, this (shorter) formula seems to work also: =IF(E3>0,EDATE(E3,CHOOSE(MATCH(CODE(D3),{65,66,77,81}),12,6,1,3)),"")

It would be a fun and educational exercise for you to figure out how it works. Give it a try and then come on back if you need any assistance with breaking it down.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03

Ask Your Question

Weekly Poll