WORKDAY Function - Invalid DATA Error
Dear I am trying to calculate a delivery date based on a date and a lead time. The leadtime is a column duration filled either by Hand or Through a Form. When the figures are coming from the form they are referring as a text. The type of the column where I want the delivery date Calculate is a date type.
I had a formula which used to work
=IF(AND([COUPA Status]@row = "Approved", NOT([Leadtime / Agreement Duration (Working Day)]@row, "N/A")), IF(ISNUMBER([Leadtime / Agreement Duration (Working Day)]@row), WORKDAY([Approval Date]@row, VALUE(LEFT([Leadtime / Agreement Duration (Working Day)]@row, 4))), "LeadTime entered Is not a Numeric"), "N/A")
That formula was not perfect as some time, People instead of introduction Numerics, started to write 1 Day, 3 days, 4 weeks etc.
To overcome the problem, I have complexed the formula to capture every scenario.
= IF(AND([COUPA Status]@row = "Approved", NOT([Leadtime / Agreement Duration (Working Day)]@row = "N/A")), IF(ISNUMBER([Leadtime / Agreement Duration (Working Day)]@row), [Leadtime / Agreement Duration (Working Day)]@row, IF(OR(FIND("d", [Leadtime / Agreement Duration (Working Day)]@row) > 0, FIND("D", [Leadtime / Agreement Duration (Working Day)]@row) > 0), WORKDAY([Approval Date]@row,VALUE(LEFT([Leadtime / Agreement Duration (Working Day)]@row, 4), WORKDAY([Approval Date]@row,(VALUE(LEFT([Leadtime / Agreement Duration (Working Day)]@row,4))*5)))
Problem I got for result "invalid data Type". I did some self troubleshooting by using the formula below and see where I was doing wrong. Used the simple formula below
=WORKDAY([Approval Date]@row,[Leadtime / Agreement Duration (Working Day)]@row),
I made a separate function in a temporary column to check the information whether the Duration Cell get a number or a text by IF(ISNUMBER([Leadtime / Agreement Duration (Working Day)]@row), "Number", "Text"). When I got "Number" as value, workday function associated in the row still returning me "Invalid data".
Please helps
thanks
Answers
-
the workday function only works with date columns. It has to both be in and reference a date column/date
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Hi Mark
Approval date column is a date Column / Leadime is a duration column / the column where the function is located is Date Column. If you look carefully I have a Workday function already in place that works. But When I tried to complexe it, due to the fact Smartsheet put a text type in duration instead of Numeric when filled in with a Form.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 352 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 135 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!