1

I'm trying to modify my spreadsheet. Below is my original spreadsheet with the formula I used. Which works great for what I needed. But my boss is wanting time stamps for 3 different actions from column C. This time is frozen and doesn't change unless column C is put to blank then added a new word.

=IF(C3<>"",IF(B3="",NOW(),B3),"")

enter image description here

I have used the following formulas for the columns, but I have run into the time changing once column C is changed. Please Help I need these times to freeze.

Formulas used in each column:

=IF(C3="ASSIGNED",NOW(),I3), 
=IF(C3="ON SITE",NOW(),J3), 
=IF(C3="COMPLETED",NOW(),K3)

enter image description here

3
  • Welcome to Web Applications. NOW() is a volatile function, meaning that it changes every time it's recalculated. In order to freeze timestamps, instead of formulas you could use a keyboard shortcuts or Google Apps Script. We already have a lot of questions about adding timestamps in Google Sheets, please spend some time learning from previous questions. If you need further help, please add a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    Jul 27, 2022 at 17:49
  • Thank you for your answer. I have looked all over trying to find something that can help me. I have never used scripts only formulas so I don't know how to even start a script for this.
    – Brenda
    Jul 27, 2022 at 18:44
  • To start with scripts, start by reading developers.google.com/apps-script/guides/sheets
    – Rubén
    Jul 27, 2022 at 18:45

1 Answer 1

0

As you have discovered, you cannot get a permanent timestamp with a spreadsheet formula, even with iterative calculation or a custom function, because formula results get refreshed from time to time. When the formula gets recalculated, the original timestamp is lost.

Search this site for [google-apps-script] timestamp to find many examples of timestamp scripts. Also take a look at yetAnotherTimestamp_ and timestampMultipleColumns_.

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.