Help with formula if cell is blank

I'm using the following formula to piece together a date in a text/number cell:

=IF(MONTH([Sale Date Form]@row) < 10, "0") + MONTH([Sale Date Form]@row) + "-" + IF(DAY([Sale Date Form]@row) < 10, "0") + DAY([Sale Date Form]@row) + "-" + YEAR([Sale Date Form]@row)

It's working great, unless the "Sale Date Form" cell is blank, then I get an "Invalid Data Type" error.

Please help with adding logic to this that leaves this cell blank if "Sale Date Form" is blank.

Thanks in advance!

Find more posts tagged with

Formulas

Comments

Andrée Starå

Hi,

Have you tried adding an IFERROR function at the beginning of the formula?

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Dartwohl

Good morning!

Yes, I have tried that by adding it to the beginning of the formula but that doesn't seem to help (just end up with invalid operation). I'm sure it's user error!

Thoughts?

Andrée Starå

Good afternoon!

Can you maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, andree@getdone.se)

Best,

Andrée

Dartwohl

I shared the sheet with you just now and here's a screenshot. Thanks again!

Paul Newcome

Andree,

Were you able to get this up and running? I was thinking of a few different ways...

The IFERROR

=IFERROR(IF(MONTH([Sale Date Form]@row) < 10, "0") + MONTH([Sale Date Form]@row) + "-" + IF(DAY([Sale Date Form]@row) < 10, "0") + DAY([Sale Date Form]@row) + "-" + YEAR([Sale Date Form]@row), "")

or the IF(ISBLANK(

=IF(ISBLANK([Sale Date Form]@row), "", IF(MONTH([Sale Date Form]@row) < 10, "0") + MONTH([Sale Date Form]@row) + "-" + IF(DAY([Sale Date Form]@row) < 10, "0") + DAY([Sale Date Form]@row) + "-" + YEAR([Sale Date Form]@row))

or the IF(ISDATE(

=IF(ISDATE([Sale Date Form]@row), IF(MONTH([Sale Date Form]@row) < 10, "0") + MONTH([Sale Date Form]@row) + "-" + IF(DAY([Sale Date Form]@row) < 10, "0") + DAY([Sale Date Form]@row) + "-" + YEAR([Sale Date Form]@row), "")

Andrée Starå

Paul,

Yes, I was!

I added an IF to check if it was empty with "" at the beginning of the formula.

Best,

Andrée

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…