WORKDAY Function - Invalid DATA Error

Options

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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/07/24
    Options

    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.

  • SebastienV
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!