Google Sheets ...how to

Reply

  #1  
Old 05-27-18, 06:46 AM
Norm201's Avatar
Member
Thread Starter
Join Date: Sep 2013
Location: United States
Posts: 8,543
Received 174 Votes on 158 Posts
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]
 
Sponsored Links
  #2  
Old 05-27-18, 07:05 AM
XSleeper's Avatar
Group Moderator
Join Date: Dec 2004
Location: USA
Posts: 25,158
Received 492 Votes on 451 Posts
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.
 
  #3  
Old 05-27-18, 07:11 AM
Norm201's Avatar
Member
Thread Starter
Join Date: Sep 2013
Location: United States
Posts: 8,543
Received 174 Votes on 158 Posts
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
 
  #4  
Old 05-27-18, 07:19 AM
XSleeper's Avatar
Group Moderator
Join Date: Dec 2004
Location: USA
Posts: 25,158
Received 492 Votes on 451 Posts
Sorry, i edited my post while thinking about it. On the road today.
 
  #5  
Old 05-27-18, 07:24 AM
Norm201's Avatar
Member
Thread Starter
Join Date: Sep 2013
Location: United States
Posts: 8,543
Received 174 Votes on 158 Posts
I guess we both learned something.
 
  #6  
Old 05-28-18, 05:53 AM
H
Member
Join Date: Oct 2006
Location: Florida
Posts: 91
Received 0 Votes on 0 Posts
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.
 
  #7  
Old 05-28-18, 06:41 AM
H
Member
Join Date: Nov 2012
Location: USA
Posts: 1,591
Received 70 Votes on 61 Posts
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
 
  #8  
Old 05-28-18, 05:03 PM
Z
Member
Join Date: Jan 2008
Location: Southeastern Pennsylvania
Posts: 3,065
Received 10 Votes on 10 Posts
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:

if (e.range.columnStart !== colToWatch || e.source.getActiveSheet().getName() !== sheetName)
whereas the other code does it in two steps:

var sh = e.source.getActiveSheet(),

if (sh.getName() !== 'Sheet1'
in other words if you replace “sh” in the above if statement with:

e.source.getActiveSheet()
you get:

if (e.source.getActiveSheet().getName() !== 'Sheet1'
and then you eliminate the statement

var sh = e.source.getActiveSheet(),
 
Reply
Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Thread Tools
Search this Thread
 
Ask a Question
Question Title:
Description:
Your question will be posted in: