Calculating on Lookup Fields for Dates

[ocmac]

Hello,

This one has me frustrated because I know it can not that hard. 😁

I need to create an Expected Ship Date field.  I have a Start Date field (formatted as Date) and a Ship Days (lookup field from another table).  I want to find out how many days prior to the start date I need to send out the package. 

First My thought was Start Date - Ship Days, that just gives me NaN message.

Then I thought I needed DateTime_Diff(Start Date,Ship Days, 'days').  That gives me a 4 digit number in some cases and NaN in other cases.  i.e. 1/3/2024 (Start Date) - 7 (Ship Days), I get 8221.  I am thinking that is good and just need to DateTime_Parse it.  So I enter,  DATETIME_PARSE((DATETIME_DIFF({Start Date},{Ship Days},'days')),'MM/DD/YYYY'), and I get #ERROR!

What am I missing?

[Sho · Accepted solution]

Hello @ocmac ,

Not like this?

IF(
  AND({Start Date},{Ship Days}),
  DATEADD({Start Date},{Ship Days}*-1,'days')
)

 The Datetime_diff function requires two dates." Ship Days" is a number, so this would not give a valid result.
Also, Datetime_parse will not work because the return value of the Datetime_diff function is a number, not a date.

[ocmac]

Ok, this worked, but I do not understand why it worked.  It doesn't seem logical to me.  I understand the Numbers vs. dates things and why Date Parse will not work or Diff will not work.  What I don't understand is the formula and why it does work. 

I am still going to make this as solution.  Thank you for your help.