#Invalid Operation Error subtracting days from another Date field

I have a sheet that has a date field that is link in from another sheet (actually 2 other sheets) that is not working as expected and I cannot seem to root cause the issue.

I have a column 75%/SD Date that I want to get 14 days before it in another column. Below are some things I have put in for the formula and the result I have gotten.

The date I see in the 75%/SD Date column is 5/30/20

=[75%/SD Date]54 - 14 (result = #INVALID OERATION)

=[75%/SD Date]54 (result = 5/30/20)

=[75%/SD Date]54 + 14 (result = 5/30/2014)

=WORKDAY([75%/SD Date]@row, -14) (result = #INVALID DATA TYPE)


The original source of the Date is a Finish date from a row in a project plan. I think I have seen this could cause an issue before but I cannot find anything on it now.


Thanks

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Interesting. =[75%/SD Date]54 + 14 should work. Smartsheets is supposed to treat the number (14) as days unless a function is used. Appears to be treating it as years for you.

    You could try: =dateonly([75%/SD Date]54) + 14

    Confirm all columns are date columns and that the date format is set the same.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Mark I get the #INVALID DATE TYPE error when I try that.