Removal of #INVALID DATA TYPE

mike.thorpe17421

I have created the following formula which auto populates a cell with a date that is 3 years before the date in another field [Date Field]:

=DATE(YEAR([Date Field]@row) - 3, MONTH([Date Field]@row), DAY([Date Field]@row))

However, when there is no date in the source [Date Field] the return message is #INVALID DATA TYPE. Does anyone know how to replace this text with new text such as "No Launch Date" or "N/A" or "0" or even better leave as blank?

Many thanks

Paul Newcome

For this you would need a second IFERROR.

=IFERROR(IFERROR(DATE(.....), DATE(.....)), "")

Paul Newcome

You would wrap it in an IFERROR function.

=IFERROR(original_formula, "")

mike.thorpe17421

Thanks Paul. The original formula was as follows:

=DATE(YEAR([Local Regulatory Authorization Date (Best Case)]@row) - 3, MONTH([Local Regulatory Authorization Date (Best Case)]@row), DAY([Local Regulatory Authorization Date (Best Case)]@row))

At which point does the =IFERROR(original_formula, "") element go?

Paul Newcome

You would drop the entire thing (excluding the very first "=") into the spot where it says "original_formula".

mike.thorpe17421

Thanks Paul. I have tried that but unless I am not understanding it doesn't work. The original formula is as follows:

=DATE(YEAR([Local Regulatory Authorization Date (Best Case)]@row) - 2, MONTH([Local Regulatory Authorization Date (Best Case)]@row), DAY([Local Regulatory Authorization Date (Best Case)]@row))

By dropping this into the spot where you reference "original formula" it looks like this:

IFERROR(DATE(YEAR([Local Regulatory Authorization Date (Best Case)]@row) - 2, MONTH([Local Regulatory Authorization Date (Best Case)]@row), DAY([Local Regulatory Authorization Date (Best Case)]@row,"")))

Where there is no date on the source column [Local Regulatory Authorization Date (Best Case)] it still come sup with #INVALID DATA TYPE. Is this because the column Properties are formatted as a Date Type?

Really appreciate your feedback here Paul.

Kind regards

Mike

Paul Newcome

You have misplaced parenthesis. All of the closing parenthesis and everything from the original formula should go in the first portion of the IFERROR. As you have it, you are including that final comma quote quote inside of the final DAY function.

mike.thorpe17421

I really do not get this at all. You mention first portion of the IFERROR but in the original message you said =IFERROR(original_formula, "") which is the first part?

The original formula is =DATE(YEAR([Local Regulatory Authorization Date (Best Case)]@row) - 3, MONTH([Local Regulatory Authorization Date (Best Case)]@row), DAY([Local Regulatory Authorization Date (Best Case)]@row))

So if it reads =IFERROR(DATE(YEAR([Local Regulatory Authorization Date (Best Case)]@row) - 3, MONTH([Local Regulatory Authorization Date (Best Case)]@row), DAY([Local Regulatory Authorization Date (Best Case)]@row)) where does the , "" go if it starts =IFERRORDATE(YEAR([Local Regulatory Authorization Date (Best Case)]@row) ........etc

Sorry to be thick here but I just dont get it?

Thanks for your help and patience.

Paul Newcome

The first portion is "orignial_formula".

=IFERROR(DATE(.....), "")

If you look at the IFERROR function, there are two parts to it (aside from the actual function text itself).

You have the portion that determines what you want to evaluate. If there is no error, it outputs whatever is in that first portion. Next you have a comma, then you have the second portion of the IFERROR that determines what is output when the first portion throws an error. Double quotes with nothing in between is a blank.

mike.thorpe17421

Thanks Paul. I know now why I wasn't getting any joy. It was because of was trying to calculate where there were months and not years. I can sort where there are round years, i.e. 12 months, 24 months, etc but where there are 9 months for example your original solution was as follows:

=IFERROR(DATE(YEAR([First Commercial Sales Date (Best Case)]@row), MONTH([First Commercial Sales Date (Best Case)]@row) - 9, DAY([First Commercial Sales Date (Best Case)]@row)), DATE(YEAR([First Commercial Sales Date (Best Case)]@row) - 1, MONTH([First Commercial Sales Date (Best Case)]@row) + 3, DAY([First Commercial Sales Date (Best Case)]@row)))

For this, it looks like it is using the IFERROR for last element but not sure where the ,"" would go where there is no date at all?

Paul Newcome

For this you would need a second IFERROR.

=IFERROR(IFERROR(DATE(.....), DATE(.....)), "")

mike.thorpe17421

Paul, you are a star !!! Thanks so much for your help and patience. It is really appreciated.

Regards

Mike

Paul Newcome

Happy to help. 👍️