Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Bug in latest version of SmartSheet

edited 12/09/19 in Archived 2015 Posts

Hello Smartsheet Community, 


I use the formula : =[Previous Week]1 - [End Date]1 to show me my variance each week when I status my smartsheet schedule.  


The latested update in Smartsheet broke my formula so that when I copy the "End Date" field into the "Previous Week" field it no longer shows as "0" when th dates haven't moved.  It now shows that the date moved by "-.70833".  


This is a bug.  I saw this in Microsoft Project when you copy dates and your "time" was different.  So if an end date shows ending at  4/3/15 5:00pm and when you copy it over it copies as 4/3/15 1:00pm.  


I suspect this happenend in your latest version because now the duration can be in minutes and hours.    


The only way I fixed this in Microsoft Project was to change the date format to show the date in a different format so that I could see the date and HOUR that the time finished and then fix it in my Previous week field.  


I don't know how many people use this formula.  It's irritating more than anything.   Any feedback on this?


Great job on adding Critical Path.  Haven't tried it yet, but nice that you added such an important feature for PMs.







  • Travis
    Travis Employee

    Hello! I am trying to reproduce this in my sheet but I have not been able to. To clarify, you have two date columns, Previous Week and End Date and a formula that subtracts End Date from Previous Week.  When the dates in these two columns are the same, you should get 0 but you are getting -.70833. Is this correct?


    I tried this in my sheet but the result was 0. Are you using a Created (Date) or Modified (Date) System column as one of your Date columns?  If you are using a System Column, then you can use the DATEONLY function which will take only the date (not time) from the cell. For example if your Previous Week column is a System column, change your formula to this:


    =DATEONLY([Previous Week]1) - [End Date]1



  • Travis
    Travis Employee

    Hello - an update to my previous post. I was not testing this in a project sheet while you likely are (project sheet dates have time associated with them). I tested in a project sheet and was getting decimals as a result. 


    Using the DATEONLY function will solve this issue for you. Try this formula:


    =DATEONLY([Previous Week]1) - DATEONLY([End Date]1)

  • Hi Travis,  Yes, thank you this formula worked.  I appreciate your help on this.

This discussion has been closed.