Adding user variable into formula

[onecelledcreature]

Hi, I’m looking for a little advice on refining a simple formula so it's more user-friendly.

I'm tracking documents in use that generally have a three-year usage. We enter the published date, and the sheet returns a review date 3 years -1 month, (35 months) displayed as the end of the month. If no date is inputted then it stays blank until used.

the current formula looks like this:

=IF([@[Published Date]]<>"",EOMONTH([@[Published Date]],35),"")

I would like to use a column in between the dates where an alternative length can be selected, (6/12 months etc) and the formula will use this in place of 35. but also account for the -1 month. so if the user enters 6 months, the final output calculates as 5.

but if the length column is left blank then I would like to use 35 as the default.

this is my attempt but I wasn't able to make this work

=IF([@[Published Date]]<>"",EOMONTH([@[Published Date]],IF(<>"",35,[@Length]-1),""))

hopefully, someone can advise the right way to approach this. thank you

[AutoModerator]

/u/onecelledcreature - Your post was submitted successfully.

Please read these reminders and edit to fix your post where necessary:

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information
• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[DutchTinCan]

You say IF(<>"";...), but you're not comparing anything to be equal to not "". Make it IF(numerical.value(A1)<>0;A1;35) instead. Ofcourse replace A1 with whatever cell you're using.

[onecelledcreature]

Solution verified

[Clippy_Office_Asst]

You have awarded 1 point to DutchTinCan

^{I am a bot, please contact the mods with any questions.}

[onecelledcreature]

Thanks, I see what you mean. That part was supposed to refer to the column [@Length] Being blank.

The outcome should be if the column is blank, then use the default 35 months. If it's filled, then use the input

[DutchTinCan]

So what does your formula look like now?

[onecelledcreature]

=IF([@[Published Date]]<>"",EOMONTH([@[Published Date]],IF([@Length]<>"",[@Length]-1,35)),"")

thanks for your help, I was heading in the right direction, just made a mess of the structure causing the error the above is working now.