Create a Static Timestamp - Google Sheets

If you're doing some project management and don't have a large budget, you can use Google Sheets as a poor man's project management tool. One concept in most project management timelines is the concept of estimated dates and actual dates. Usually you'd input the dates manually for these dates, but it would be nice to have the actual date entered "auto-magically" based on the value changes of another cell (i.e., when a percent complete is entered). But you don't want the actual dates to change after the initial value is triggered in that cell; you want the time stamp that updates only if data is entered in some cell.
This video covers a workaround using iterative calculations and sort of breaks a rule in spreadsheet design where you don't want circular references. In this case, we'll have to change the Google Sheet setting to allow for it. See the video to learn how to do this.
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltrain...
📚 Excel Books & Tech Gear ➜ www.amazon.com...
⚙️ Tools: Screencasting ➜ techsmith.z6rj...
⚙️ Tools: Microsoft Office ➜ microsoft.msaf...
⚙️ Tools: TubeBuddy ➜ www.tubebuddy....
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to / doughexcel
#excel
#msexcel
#doughexcel

[@LoreneLanier]

This is great solution for a simple timesheet. However, I would echo the comments from Basil K a few months ago. I tried having a pulldown menu or a check box in the trigger cell. Everything looked great they day I was using the sheet, but when I open the sheet on alter day, all the stamps triggered on the same day default to having the same timestamp. This was true of the pulldown or check box triggers. Sad to say, I don't know enough to figure out what is happening...

[@thenusht]

its so simple i can't believe it. I have been racking my brain on this for hours. thank you so much!!

[@DougHExcel]

Thanks lance harmon, glad it helped!

[@basilkezios8457]

The least I can say is BRILIANT! The only issue I have is that the method does not seem to like dropdown menus. It will update after I select on the dropdown menu after I have made my choice. Not a big issue. Thank you!

[@DougHExcel]

Hi Basil Kezios, you're welcome!

[@Lombey84]

I very much like the way you solved this problem. Works great.Thank you!

[@DougHExcel]

Glad you thought so, thanks!

[@letemknow69]

Amazing I have been looking for this for over an hour, finally you saved me. Thank you

[@DougHExcel]

Glad I could help

[@beabeeeee]

I have to tell you that I have been working this with script approachment for a static timestamp for the last 2 days but it's quite difficult if you didn't follow the script lesson from the start, and then I try your way, this works like magic. THANK YOU! You are a lifesaver :")

[@DougHExcel]

Glad to hear it helped!

[@Marco-li4qi]

I have a problem...when there is the value in the another cell before the formula, the formula don't works

[@sofirivictor7211]

hi, i entered =IF(K2271"",if(Q2271="",NOW(),Q2271),"") but noticed that my googlesheet updates when data in entered in the next cell. the data updates across all

[@myinamei8884]

Thanks!!! That was very helpful!

[@DougHExcel]

Glad it helped!

[@AllyPenguin]

Thank you!! It worked for me.

[@DougHExcel]

You’re welcome!

[@zurahamid2524]

HI Doug, I did all the steps. It works great on the day. But the time changes the next day. Is there a formula where the static time become permanent. FYI I'm using the NOW() function for staff attendance.

[@panick88it]

for me the same. did every step but if I reload the sheet the day after, the date is updated as well... toughts?

[@victoriacosta7220]

@@panick88it Me too!

[@mismanager934]

Date is changing on every day, it is due to some updates done by Google...need to wait

[@MrMike10084]

[@RupanagudiRaviShankar]

Sir, thanks a lot for this tutorial. was wanting exactly this type of data input for some personal data analysis of mine. with regards

[@DougHExcel]

Glad it helped you and thanks for commenting!

[@drippinghere]

This was exactly what I was looking for! Thank you :)

[@DougHExcel]

You're very welcome!

[@uchennaanunike9019]

Hi doug, how about if you what to apply it to more than one cell, also how do you avoid tempering assuming the sheet is accessible to many people

[@michaeldouglas1052]

Thank you so much! It help me a lot!

[@DougHExcel]

You're welcome!

[@TalmadgeMcGhoulaghy]

AWESOME, AWESOME, AWESOME! Thanks so much.

[@DougHExcel]

You're very welcome!

[@sukhdevsharma9796]

It was helpful. Thank you for sharing this tricks.

[@DougHExcel]

My pleasure 😊

[@gurumoorthyganesan8141]

Thanks a lot man.. !!!!!!!

[@DougHExcel]

You’re welcome!

[@eventsbanquets4386]

Thanks Doug. However, Time stamp changes the next day when i reload. Anything missing?

[@wanyeglennasaah2558]

OK TERY TO CARRY THE CELL FROM B1 TO B5 AND SEE HOW THE TIME WILL AUTO RESET

[@alessandrofelluga2910]

thank you

[@DougHExcel]

You’re welcome!

[@xlgablx]

Just used your strategy. Tomorrow I'll see if it works for me. If it works, I'll subscribe and like the video. Thank you so much

[@DougHExcel]

Great, thanks!

[@CTal0191]

what if it's a checkbox instead of typing something to trigger the formula?

[@CTal0191]

i want it to be static even after you uncheck the checkbox. pls help

[@mr_shanes]

will this give correct date (and time) if data is programmatically changed in cell C2? (eg via an import done through apps script)

[@wanyeglennasaah2558]

what if you change the cell position

[@wanyeglennasaah2558]

is it possible to make it fix when changing the row position

[@CastlebooksWarehouse]

Hi Doug, this is exactly what I am after but my Date is reading 30/12/1899 00:00:00 any idea why this would be happening?

[@CastlebooksWarehouse]

=IF(C4"",if(D4="",now(),D4),"") is the code im using

[@DougHExcel]

Could be the display format. This may help. Change Dates to Make it Easier to Read kzbin.info/www/bejne/d5bdn2iAnrx4eac

[@nisargagza]

Good trick

[@DougHExcel]

Hi Let's Learn, thanks for the comment!

[@chasetuttle8278]

Its like Google finds out we are trying to do logical arguments and they stop letting it work. I did exactly what you did and can not get it to work

[@abinayas6151]

thanks a lot

[@DougHExcel]

You’re welcome!

[@user-md4mb5so8h]

Super Man, U rokssss

[@DougHExcel]

Thank you so much 😀

[@berkyonet5192]

ur the one

[@DougHExcel]

Thanks!

[@patientincrease7203]

Can you get a timestamp the last time a cell was updated?

[@DougHExcel]

There's not a function that I know of, but there's scripts that could be used for it...I'm not familiar with the scripting.

[@ezpzdz8155]

Thank you sir that was very helpful 🙏 Edit: I noticed that the values get to 0 after I reopen the sheet :(

[@DougHExcel]

Hi Ezpzdz, thanks for the comment!

[@nssdesigns]

That's a lot simpler than writing javascript in App Script and doesn't the user to Allow Scripting

[@DougHExcel]

Hi Northern Exposure, thanks for the comment!

[@philipnicolaou5192]

Hi Doug, Love this, its so much better than the scripting approach. I would like this function to be triggered by a checkbox. I have changed the B2 to B2="FALSE", so the formular reads =IF(B2="FALSE",if(C2="",today(),C2),"") but it doesn't work.

[@karanvats4830]

Don't use "FALSE", use FALSE without double commas.

[@warblender5657]

Hi philip,,i have the same idea with you to use a check box,,im using this formula =IF(B2FALSE,IF(C2="",NOW(),C2),"")

[@philipnicolaou5192]

@@warblender5657 solid & reliable approach. simple enough to be deployed by any one.