How To Create Static Timestamps in Google Sheets

[@HashAliNZ]

Go on, be honest - did you ACTUALLY know all those hotkeys at the beginning?

[@user-sq2ip3lj6i]

Wow. Super cool. 👍👍

[@TilakConrad]

yet another great tutorial - been missing your videos - thanks and all the best

[@HashAliNZ]

Thanks for sticking around!

[@emilygray9868]

Thanks for your videos, I am trying to have a static timestamp, but also with an onEdit that moves the rows, depending on their priority. Is this possible? I have timestamp in column E, dependant on column D having "Completed" then it moves to the bottom. Then anytime I change the date, it moves. I've tried your suggestions alongside: function onEdit(e) { const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName("To Do"); const range = sheet.getRange("A2:E1000"); range.sort([4, 1]) // ID } but it doesn't seem to like it. Thanks :)

[@firewmulugeta1611]

Love your videos I want you to make a tutorial about all about google sheets Please

[@HashAliNZ]

Absolutely. More on the way!

[@firewmulugeta1611]

Thank you very much 🙏

[@erickperez2052]

With the lambda method, after you refresh can you add new data?

[@annellbrodeur4890]

This is so helpful, thank you! Is there a way to do this but for a whole array? I am using this for when an item in a row was created but I also want a timestamp for when a specific cells in the row have been edited.

[@HashAliNZ]

Hi Annell, The timestamps using the LAMBDA function won't work for a whole array, however it will work with the Script shown in this video.

[@AmyLolaM]

This is great. Thank you! Quick question -- using the lambda method, how do you change the timezone?

[@HashAliNZ]

Do you mean if you have people across the country in different timezones clicking those tickboxes? If that's the case, you'll need to create a script that determines each user's timezones. If it's just that you want to change to your timezone and noone else is using the script, then head up to File > Settings > and choose your timezone from there.

[@AmyLolaM]

@@HashAliNZ Thanks much for the reply! It was the latter. Super simple it turns out. Haha.

[@hausofwen]

Hey is it possible to do this for when the data was entered but not edited? for example if I made a typo or need to adjust spelling a few days later, I want the timestamp to stay the original date the data was entered and not change to the day I corrected the spelling. Thanks

[@HashAliNZ]

Yeah it should work with the len(y) Formula

[@hausofwen]

@@HashAliNZ no I tried it and the timestamp updated to the exact time I made the edit

[@HashAliNZ]

Oh you're right! I just tested it and same thing happened. This is because the target cell is being updated. Instead, we'd need a script to keep the time stamp the same. Open up Apps Script and paste this in. I've set it to work on Sheet1, when you change the cells in Column C it'll put the timestamp into column D. Change the columns and tab name to make it work for your purposes: function onEdit(e) { var sheet = e.source.getActiveSheet(); var range = e.range; if (sheet.getName() == "Sheet1" && range.getColumn() == 3) { var row = range.getRow(); var cellC = sheet.getRange(row, 3); var cellD = sheet.getRange(row, 4); var valueC = cellC.getValue(); var valueD = cellD.getValue(); // If C cell is edited and is now empty, clear the D cell if (!valueC) { cellD.clearContent(); } // If C cell is edited and D cell is empty, record the timestamp in D cell else if (!valueD) { var currentTime = new Date(); cellD.setValue(currentTime); } } }

[@tehrissa]

Can you explain how to do this with a pulldown list?

[@HashAliNZ]

Sure: =lambda(x,y,if(y="Text from Dropdown List",x,))(now(),B8)