Counting for a Blank Cell

Hello!

I have this formula that works great:

=DATE(YEAR([Completion Date]3), (MONTH([Completion Date]3) + 3), (DAY([Completion Date]3)))

But if my date in "[Completion Date]3" is blank it throws off my whole formula. What can I add so it stays blank if I have no date in "[Completion Date]3".

THANK YOU!

Find more posts tagged with

Healthcare

Formulas

IT & Operations

Comments

Andrée Starå

Hi Lisi,

You need to add an IFERROR function.

More info: https://help.smartsheet.com/function/iferror

Let me know if you have any questions!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Mike Wilday

To be more specific you can wrap the entire formula in the IFerror function like this...

=IFERROR(DATE(YEAR([Completion Date]3), (MONTH([Completion Date]3) + 3), (DAY([Completion Date]3))),"Insert Date")

This will give you the value of "Insert Date" if there is no date in the Completion Date field. If you want it to be blank, you can Delete the text Insert Date and leave the quotation marks.

Lisi

Hi Mike,

Thank you very much that works perfectly!

Mike Wilday

You're welcome. Glad I could be of assistance.

Paul Newcome

You could also use your current formula in an IF statement to say if the completion date is an actual date, then run your formula, otherwise leave blank.

=IF(ISDATE([Completion Date]3), your formula goes here, "")

Mike Wilday

So true Paul! That's a good idea too.

Lisi

This is perfect, I was able to answer another question with this also!

THANK YOU PAUL!

Paul Newcome

I use this process a lot to build nested IF statements. Sure you could use an AND statement if you have multiple requirements, but a lot of the time I haven't realized the need for it until I have already built the bulk of my formula. In those cases, I'll just wrap it in one more IF function. This also provides the ability to account for things other than errors.

Sign in to join the conversation!

Quick Links

Trending discussions

Break Up Multi-Entry Row
I'm using a form entry (into an Intake Sheet) in which someone can select multiple locations for the same Initiative. I'm looking to copy the row to a different Master sheet, but would like to have the same row copied multiple times, once per location selected. Intake Sheet: Desired Result in Master Sheet: Basically, I…
Set contents of two columns into 1
Hi - trying to figure out a formula that combines two columns into one long column, maintaining contents in multiple rows. Noting that the number of items in each column will change as these are each using the index/distinct to summarize a separate sheet. For example: Thanks for any help!
Unparsable formula; Sheet summary SUMIF formula
Can anyone help why this formula is coming up as unparseable? I'm creating a sheet summary, where it totals the Estimated GP (Est. GP) column if the Status column has the "Quoted" drop-down option is selected. Formula i've used: =SUMIF(Status:Status,"Quoted",Est. GP:Est. GP) Thanks a lot!