Tracking variances between baseline and actual dates

Hello, I have copied a plan into Smartsheet from MS Project and have followed the instructions in https://www.smartsheet.com/blog/smartsheet-tips-tracking-baseline-schedule-variance 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

 

Comments

  • 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: https://help.smartsheet.com/functions and https://help.smartsheet.com/articles/2476171-create-and-edit-formulas-in-smartsheet

    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!

    Suzy

     

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