Tracking variances between baseline and actual dates


Hello, I have copied a plan into Smartsheet from MS Project and have followed the instructions in to set up a means of tracking the variance between baseline and actual start and end dates. However the message # Invalid Data Type appears in the variance cell where I have put the formula. 

As far as I understand Smartsheet (I am a new user) I have set the column settings correctly (date for date columns and text/number for the variance columns). 

Any ideas, including pointing out the obvious, would be much appreciated!

Thank you



  • Hello! 

    It would help if you could copy the formula you're using or an example to view. Also, make sure your column property type is set to Date/Time in the appropriate columns. The formulas you use in SmartSheet are not necessarily the same as you would in Excel.

    Have you seen these pages? They've been a huge help for me learning formulas: and

    Keep us all updated!

    Best regards, TJ

  • Hi Tracie, thanks for responding. Yes indeed, it would have helped if I had posted the formula! I have now tracked down what I was doing wrong. I was using the DATEONLY function and thought that it applies to the whole formula, but it only applies to the [Start] column value, to eliminate the time part of the Date/time format. So, I was typing

    =DATEONLY([Start Date]1 - [Planned Start]1)

    instead of

    =DATEONLY([Start Date]1) - [Planned Start]1

    Grrrrr ....

    Thanks again!

  • swh@dr
    swh@dr ✭✭

    I am also trying to set this up and getting the same error. When I check the column types for Planned Start and Planned Finish, both are set to "Date" and not DATE/TIME. I only have the type "DATE" in my drop down. 

    My forumla is =DATEONLY([Start Date]2 - [Planned Start]2)

    Please help!



    Screen Shot 2018-09-05 at 10.59.20 AM.png