Google Sheets ...how to

Using GOOGLE Sheets. When a value is inputted into a cell I want another cell to automatically insert the date of that cell input.

For instance if cell 1A gets any value (for whatever reason) I would like cell 1B to automatically post the date cell 1A was inputted. I can do that with the TODAY function, but the next time I open the spread sheet the date will default to the current day. I want to preserve the history of each cell input. So how can I stop the date cell from formatting the current date.

The spreadsheet shown tries to preserve the calibration history. The date column currently has the formula =if(D133="","",today())
same thing with the time stamp column =if(D133="","",NOW())

I have to change the proceeding inputs manually. How can I preserve the dates and prevent them from changing from day to day?

[IMG][/IMG]

 

I think you might use Now rather than Today. Or does now return the time? Not at home to try it.

I bet you have to use a $ string function to turn today into text.

 

XSLP'R,

Of course. I did that for the time stamp and just formatted for time. How obvious! I should've done the same for the date stamp and formatted for date. !

Sometimes I amaze myself for not being able to see the forest through the trees.

Thanks.

Hold on...Won't the same thing happen?

From the GOOGLE search...

For example, worksheets recalculate each time they are opened or when certain events occur – such as entering or changing data in the worksheet – so if the date and/or time is entered using the NOW function, it will continue to update.



There is no option within the program for turning off recalculation of volatile functions.
Keeping Dates and Times Static

If having the date and/or time continually change is not desirable the options for entering static dates and time include typing the date/time in manually

 

Sorry, i edited my post while thinking about it. On the road today.

 

I guess we both learned something.

 

I had the same requirement and solved it by writing a function to be applied to the sheet. Here's the code I used which can be modified for your need. It's been a while since I did this so you will need to determine how best to implement the code.

function onEdit(e) {
var sheetName = 'Sheet1'; //name of the sheet the script should work on
var colToWatch = 2 // watches for edits made in col B
var colToStamp = 5 //timestamp in col E
if (e.range.columnStart !== colToWatch || e.source.getActiveSheet()
.getName() !== sheetName) return;
e.source.getActiveSheet()
.getRange(e.range.rowStart, colToStamp)
.setValue(e.value ? new Date() : null);
}


Note that posting removed leading spaces used for readability.

 

You're looking for a timestamp of when a colum is edited?

Check how to timestamp a entry and keep that current date of entry

 

However it looks like the solution at that link is the same as harvx’s in post #6. And if you are a purist (and I’m not-lol) IMHO harv’s is a little more elegant. His code to check that you are on the target sheet before you make the Cell change is completely embedded in the If statement:

whereas the other code does it in two steps:

in other words if you replace “sh” in the above if statement with:

you get:

and then you eliminate the statement