Problems using a formula as a statement_if_true in an If formula

[SophieYaldwyn]

Hi all,

I am currently doing a study module on designing and producing complex spreadsheets and am having trouble with a formula I am trying to create in my project in cell B35.

I am trying to create an IF formula to show a blank cell if there is no value in cell A35, for instance if the month is February.

I have tried all variations I can think of and done a lot of googling but cannot find if it is even possible to return a formula as an answer.

The current formula in the cell is shown in the screenshot. I have tried inputting =IF(A35=31,"=DATE($B$3,$B$2,$A35)","") and have tried removing the quotation marks around the statement_if_true, adding parenthesis around the statement_if_true, spaces etc. 

Cell A35 contains the formula =IF(OR(B2=1,B2=3,B2=5,B2=7,B2=8,B2=10,B2=12),"31","") if that could affect things maybe?

I also want to add a cell up the top to state if it is or is not a leap year to then refer to, to make cell A33 show or not show when it is February and am wondering if there is a way I can make that Leap year cell value be determined by what year it is in cell B3?


Thank you so much in advance!
Sophie

[#2]

@SophieYaldwyn 

You are clearly trying to do something with dates and days of each month. Might I suggest you back up and put words to what it is you want this spreadsheet to do, for the months consisting of 30 days, those with 31 days, and then Feb, whether in Leap Year or not.

It's entirely possible that what you're trying to achieve can be done without nesting a formula within an IF (which I strongly suspect is not directly possible anyway).

I'm also attaching a spreadsheet that I have in which I use various date functions--there are many--to calculate last days of any month given just the year and month, as well as other things (The original is part of a larger workbook that I use in tracking investments; some instruments expire on the third Friday of each month, for example, so some of these formulas come in handy in that regard).

I include this just to stimulate some thinking...about possible other ways to accomplish what you want to. These do require the most recent version of Excel.  Note: This spreadsheet doesn't have a lot of documentation, but it sounds like you're familiar enough with Excel that you should be able to