How To Create Static Timestamps in Google Sheets

Static Timestamps In Google Sheets. Yep. That's all. Good luck!
🚀 Boost Your Spreadsheets Now ➡️www.sheetanalysis.com
All Products and Gear Mentioned in this Video:
Excel Software
Google Sheets
🔗 Subscribe to Our Channel for More Excel and Google Sheets Tips:
WORK WITH ME:
Google Sheets Fundamentals Course for Beginners: www.formula24.xyz
Spreadsheet Consult, Fix, or Build (via Fiverr): www.fiverr.com/s/dyB6gg
Business Spreadsheet Audit: www.sheetanalysis.com
Scripting Help from an expert (via Fiverr): www.fiverr.com/s/L5zBWa
DISCLAIMER
This video is not sponsored. Some product links are affiliate links which means if you buy something we'll receive a small commission.
----------------------------------------------------------------------------------------------------
⏳ TIMESTAMPS
----------------------------------------------------------------------------------------------------
0:00 Keyboard Shortcuts
0:30 The NOW() function
1:02 The Popular (But Risky!) Method
3:23 Why You Shouldn't Use That Method
3:48 Formulas
6:04 Scripts
----------------------------------------------------------------------------------------------------
#ExcelTips #GoogleSheets #ProductivityHacks #Timestamps #DataEntry
ABOUT:
This channel is your ultimate destination for mastering Google Sheets. From basic functionalities to advanced techniques, we cover everything you need to streamline your workflows and make your data work for you. Whether you're a beginner or an experienced user, our tutorials, tips, and guides are designed to enhance your spreadsheet skills and boost your productivity.
💬 GOT A QUESTION?
↓↓↓ Leave a comment down below ↓↓↓

[@HashAliNZ]

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

[@erickperez2052]

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

[@TilakConrad]

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

[@HashAliNZ]

Thanks for sticking around!

[@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.

[@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 🙏

[@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)