When Date field is blank #INVALID DATA TYPE appears
I am using the following column formula to project 10 working days from the time a form is sent. How to I change the formula to remove the #Invalid Data Type when the Date field in Date Payment Request Form Sent is blank?
Best Answer
-
You'll want to wrap your formula in an IFERROR.
=IFERROR(WORKDAY([Date Payment Request Form Sent]@row, 10), "")
This says, if there's error (from trying to evaluate an empty cell or non-date value with the WORKDAY formula), instead of showing the error message, just leave this cell blank (empty quotes).
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
You'll want to wrap your formula in an IFERROR.
=IFERROR(WORKDAY([Date Payment Request Form Sent]@row, 10), "")
This says, if there's error (from trying to evaluate an empty cell or non-date value with the WORKDAY formula), instead of showing the error message, just leave this cell blank (empty quotes).
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
In this case - I tried to use the suggestion from Jeff, but it changed the error from #invalid data type to #incorrect argument set. What am I missing, please?
-
Hi @D Barnes
I hope you're well and safe!
Can you paste the formula you're having issues with here?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Andrée Starå - thank you:
=NETWORKDAYS([Target Announcement Date]@row, [Actual Completion Date]@row)
then, I made the if error (here is the new formula that receives the #incorrect argument set: =IFERROR(=NETWORKDAYS([Target Announcement Date]@row, [Target Completion Date]@row, ""))
-
Happy to help!
Try this.
=IFERROR(NETWORKDAYS([Target Announcement Date]@row, [Target Completion Date]@row, "")
Did it work?
✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
No, it didn't work. I still received the #incorrect argument set.
-
Can you share the formula you tested here?
Also, ensure that the column is a date-type column.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Here it is:
=IFERROR(NETWORKDAYS([Target Announcement Date]@row, [Target Completion Date]@row))
The column is set to text. Should I change this to date? I'm not sure how that would work.
-
Try my formula exactly as it is, copy and paste. Yours wasn't as mine.
=IFERROR(NETWORKDAYS([Target Announcement Date]@row, [Target Completion Date]@row, "")
Did it work?
✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!