Bug in latest version of SmartSheet

LPiccini

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

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

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)

LPiccini

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