#INVALID VALUE with date formula

Hi there,

I have a sheet with 3 date columns - one for inputting the date when a QC investigation was opened, one for calculating a deadline for QC which is 1 month from the opening date, and one for calculating a deadline for QA which is 1 month plus 15 days from the opening date.

In certain instances the formulae are working fine, with other dates it returns #INVALID VALUE error. It appears that when the outcome of the date is between the 2nd and 15th of any month, the error is given.

Formula for Date Due (QC Conclusion):
=DATE(YEAR([Date Initiated]@row), MONTH([Date Initiated]@row) + 1, DAY([Date Initiated]@row))

Formula for Date Due (QA Conclusion)

=DATE(YEAR([Date Initiated]@row), MONTH([Date Initiated]@row) + 1, DAY([Date Initiated]@row) + 15)

The same outcome occurs if I shift the formula to calculate only 15 days from the Date Due (QC Conclusion) date instead. Column types are all Date (except the first column).

I think that the error is somehow related to the part of the formula mentioning the number 15. Is there a workaround to this please?

Tags:

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!