TODAY() Function is Replacing Previous Date

Allison_S
Allison_S ✭✭✭
edited 12/09/19 in Formulas and Functions

Hello, 

I have a column in a production spreadsheet that I am utilizing to enter the date of which one of our production members completes an order. The columns are set up as follows - one column is an initials column where a production member selects their initials once they complete an order. The second column contains the formula "=TODAY()" which populates the current date of which a production member enters their initials (based off of the initials column). 

What I am discovering is that this function is operating as expected throughout the week, but on Monday morning - anything that had the date of the Friday prior is replaced with Monday's date when the first person logs onto the production sheet. Example - production team completes orders on Friday 10/26, when the first user logs into the sheet on Monday 10/29, all of the orders with the date of 10/26 are replaced with 10/29 on the first instance of someone logging onto the sheet. What can I do to stop this, or what other function options are there to gather the date and make it stick? 

Any ideas or advice of how to prevent this would be greatly appreciated!!! Thank you!

Comments

  • Brian W
    Brian W ✭✭

    Yes, the TODAY() function will always update to today's date. I believe the only way to enter the date permanently is to do it manually.

  • Allison_S
    Allison_S ✭✭✭

    Thank you for your knowledge, I appreciate the advice.

    If this is true, why does it retain previous dates on every other order except if it is a Friday? Monday-Thursday dates stick with the date the order is completed. If the today function always defaults to the current date - all of the dates in my spreadsheet would always be the current date? This is not the case? 

    Any further ideas would be greatly appreciated. :-) 

    Screen Shot 2018-11-12 at 1.39.33 PM.png

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Can you paste the full formula into this article so we can help you dissect and determine why it is changing? 

  • Allison_S
    Allison_S ✭✭✭

    Sure, here it is. 

     

    =IF([Artist - Pints]1 <> "", TODAY())

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Interesting. The item should update whenever the sheet is opened. So if you are opening the sheet only on Mondays, then you might only be seeing that change on the day you open it. Are you opening it consistently during the week? Based on the formula you posted anytime you open the sheet and it identifies that the Artist-Pints is not blank, it should update that field to the current date. 

     

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Have you been able to determine if someone is hard-coding those dates at some point? Have you tried - right-clicking on a cell history that hasn't changed and seen if someone manually set a date? '

     

  • Allison_S
    Allison_S ✭✭✭

    I did click on the cell history, and it shows the first person to log onto the sheet Monday morning as updating the date, but not on all of the date cells (which is strange). We use this sheet daily for our production, which is why I am confused as to the date change being random throughout the rows and is only consistent with Friday/Monday instances. 

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    On one of the rows that didn't change... can you click into it to see if it is still set up as a formula? Also check to see if the Artist Pints is blank. If it is blank in those same rows. 

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!