#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?
Best Answer
-
Try these instead…
QC (also accounts for going into the next year):
=IFERROR(DATE(YEAR([Date Initiated]@row), MONTH([Date Initiated]@row) + 1, DAY([Date Initiated]@row)), DATE(YEAR([Date Initiated]@row) + 1, 1, DAY([Date Initiated]@row)))
QA:
=[Date Due (QC Conclusion)]@row + 15
Answers
-
Try these instead…
QC (also accounts for going into the next year):
=IFERROR(DATE(YEAR([Date Initiated]@row), MONTH([Date Initiated]@row) + 1, DAY([Date Initiated]@row)), DATE(YEAR([Date Initiated]@row) + 1, 1, DAY([Date Initiated]@row)))
QA:
=[Date Due (QC Conclusion)]@row + 15
-
Many thanks, it worked :)
Regards,
Justin -
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!