WORKDAY Function - Invalid DATA Error

SebastienV

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

Mark.poole

the workday function only works with date columns. It has to both be in and reference a date column/date

SebastienV

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.