Learn how to automatically create a timestamp when data is entered in Google Sheets & Excel using iterative calculations and built in functions. #Timestamp #Excel #Google Sheets
Пікірлер: 129
@omdcosta23313 жыл бұрын
Thank you for the excellent video ! A 100% non coder like me was able to figure out how to implement an autostamp easily. Big ups to you .
@ExcelGoogleSheets3 жыл бұрын
Great to hear!
@mdamirhossain93764 жыл бұрын
Just awesome. I'm new in G.Sheet, but working in Excel for long. I never know a circular reference can work. Love it.
@ExcelGoogleSheets4 жыл бұрын
Great to hear!
@Allen-L-Canada3 жыл бұрын
just what I was looking for today! I use it to record daily portfolio totals. Work wonderfully!!!
@dustinkeier91762 жыл бұрын
Thank you SOOOO very much. I have watched several videos and had explanations of the formula, but none noted a very key piece that helped me solve the issue I was having. I needed to find a way to time stamp a cell when "in progress" was entered, and then timestamp another cell when that cell was changed to complete. And It was your explanation of how the IFS formula broke down that finally clicked in to place that pivotal info. in the second time stamp I just needed to enter an additional logical test to test if it was "in progress" and leave blank, so that when the IFS reached the end of the tests it would time stamp accurately. Thank you again so very much!!!
@ExcelGoogleSheets2 жыл бұрын
:)
@santsangwornrachasup6613 Жыл бұрын
Always very helpful. Simply but amazing technique. Thank you!!
@AshleyAiki Жыл бұрын
This guy was awesome! I also figured out how to use the checkboxes using this formula here: =ifS(A2=FALSE,"",B2="",NOW(),TRUE,B2) - that took too long but it works. Thank you!
@ExcelGoogleSheets Жыл бұрын
👍
@emmikeyzewa41482 жыл бұрын
many thanks and God bless, this has taken me a step foward
@fredrikpahlsson4 жыл бұрын
As always - Splendid! Just one small question; If one use a checkbox (=TRUE or FALSE) in A2 - How do one solve the "else" case (that now is TRUE, which don't work when using the checkbox with the values TRUE or FALSE)? Any suggestions would be much appreciated.
@IceCreamHouseLU3 жыл бұрын
Wow this is really great. By watching your tutorials, I have made an inventory system shared to our stores (we have 8), all I have to do is protect ranges from the devices/accounts they are using. (We started our business records managements system from the classical paper and forms method) What if I want to have the last modification to be displayed on the timestamp?
@antoniodelia42714 жыл бұрын
Thank You, Thank You, Thank You so much!
@richardmhain4 жыл бұрын
These should be useful now that we're aware of timestamp and iteration. Wondering how auto recalc option affects iteration. Will give it a try sometime. Thanks!
@ExcelGoogleSheets4 жыл бұрын
Great!
@adityasvasisht47434 жыл бұрын
You are a LEGEND! I love your work! So I tried doing this same tutorial in another way using App Scripts without the need of circular references. I made this simple function function timestamp() { const date_time = new Date(); return date_time }; now on the google sheet, I made two columns with headers "TimeStamp" & "Name" "A2" cell would have the formula "=if(B2="","",timestamp())" Now if you type anything in B2, a timestamp gets added. Another cool thing is if you re-edit B2, the previous timestamp is still stored and does not get re-written.
@ExcelGoogleSheets4 жыл бұрын
Cool.
@omarkraidie4 жыл бұрын
Ahh thanks.. gotta learn JS so goood!
@flotanteu3 жыл бұрын
Used Aditya's script it and worked great, but the next day the timestamp updated when I opened the sheet from another account. Maybe it has something to do with the way Google Sheets populate itself when not in the chache, or something of the sort.
@jeromenaron2962 Жыл бұрын
@@flotanteu do you solved this problem sir?
@flotanteu Жыл бұрын
@@jeromenaron2962 Yeah, don't call the app from a formula, use an onEdit app that checks for the desired conditions and sets the stamp as a value. For full tiemstamp: var today = new Date(); /*desired range here*/.setValue(today); it did the trick, I don't know why it must store the stamp in a var first, "it just works". For stamping just the date, you need to set the time at zeroes. /*desired range here*/.setValue(new Date(today.setHours(0, 0, 0, 0))); For stamping the current time only, this is not optimized but saved me all the java timezones drama: e.range.setFormula('=NOW()-TODAY()'); var ahorita = e.range.getValue(); e.range.setValue(ahorita); e.range.setNumberFormat('H:mm');
@apljaaay3 жыл бұрын
You saved me! More tips and tricks please!
@isikeratli4 жыл бұрын
I wish you could post videos every day :D thank you for spreading your knowledge!!
@ExcelGoogleSheets4 жыл бұрын
Thank you!
@bocobox4 жыл бұрын
I am always looking forward to your next video. you really make my eyes open.
@ExcelGoogleSheets4 жыл бұрын
Glad you like them!
@vinay1033 жыл бұрын
I love this video. And thanking you very much.
@unlearnbusiness Жыл бұрын
Awesome! Very detailed explanation.
@enisuherni95413 жыл бұрын
Thank You, Thank You, easier than the scripts
@MrSleep-jv3gh3 жыл бұрын
Timestamp Still change if i fill another cells then i did this =IFS(A1="";"";A2="";NOW();A2;A2) Its works 😅 Thank you
@canzoneri810 ай бұрын
Excellent! Thank you!
@xaquison2 жыл бұрын
You are GOOD! Extremely useful.
@ExcelGoogleSheets2 жыл бұрын
Thank You!
@user-op6zr5wc5h2 ай бұрын
This helped so much, thank you!
@kennethforbin56043 жыл бұрын
I really like this guy. He picks the right things and explains really well. Thanks
@ExcelGoogleSheets3 жыл бұрын
I appreciate that!
@justfly25254 жыл бұрын
I've never used IFS... I'm always surprised what I learn on here. Thanks for the videos!
@ExcelGoogleSheets4 жыл бұрын
Thank You!
@janalay5759q3 жыл бұрын
Big Big Big Thanks!!!
@aprendendogooglesheets80402 жыл бұрын
You are the best!!!
@elttio4 жыл бұрын
Thank you very much for that 🙏
@ExcelGoogleSheets4 жыл бұрын
Thanks for watching!
@Puner543 жыл бұрын
Damm! You are good!!!
@jonathanrichardparengkuan5693 жыл бұрын
HI... really helpful with ur videoo.. but want to ask if I want to combine it with array function how to write that formula ?? wait for ur reply ^^
@gabrielakakporo97832 жыл бұрын
Very nice tips there. How about getting the current time stamp whenever there's edition?
@MrLilger4 жыл бұрын
do you know if any data import query can modified the data iteration?
@Andreterragt Жыл бұрын
Awesome videos!
@ExcelGoogleSheets Жыл бұрын
👍
@PepitoGrilloCanarias4 жыл бұрын
Great as always
@ExcelGoogleSheets4 жыл бұрын
Thanks again!
@senthil47074 жыл бұрын
Hi, This is exactly what I was searching for, thanks a lot for sharing this. I have also subscribed ur channel.👍
@ExcelGoogleSheets4 жыл бұрын
Thanks for the sub!
@mohsinahmadkhan5153 жыл бұрын
Hi it waa wonderful and a big help.. But i need where we dont need to necessarily erase the cell data and then add again... If just over writing can work that will be awesome
@ajayshakti58183 жыл бұрын
Thanks..
@matthewolson74043 жыл бұрын
So how would we set it up to update the time, when we make a change to google doc sheet? I mean the whole sheet, any change, displayed in one cell expressing the changed date, I know under Spreadsheet settings > Calculations > Recalculation "On change", but that is not the case it will update the time regardless any time you open the document or it's idle after a while the time still changes. I tried selecting the whole sheet and excluding the one cell that would show the modified time, but it's not working it keeps giving me #VALUE, any thoughts or can you help?
@abhishekdhyani65204 жыл бұрын
Tqq so much for this video sir....I lv it....your Chanel help me a lot sir...👌👌
@ExcelGoogleSheets4 жыл бұрын
Nice to hear.
@pichit.raetai4 жыл бұрын
Thank you. I m big fan
@ExcelGoogleSheets4 жыл бұрын
Thank you!
@augustomigliori8757 Жыл бұрын
W O N D E R F U L !!! Can we actually store data with iterative calc?
@argonaut1193 жыл бұрын
Gotta say, my friend, you're really GOOD. Also, concepts well explained. Congrats I need to learn Apps Scripts next. What is your advice: what's the best way to learn Apps Scripts.
@ExcelGoogleSheets3 жыл бұрын
Start here kzbin.info/www/bejne/l4GtXmWLaZp1pqM
@asuspisi75742 жыл бұрын
Great video.. thank you for sharing this video.. i have a question, what if i want to add a worksheet and i also put timestamp.
@nodrama6832 жыл бұрын
I need some help and hopefully some can tell me how to do this pretty quick. I am trying to trigger a timestamp (that doesn't change) off of another calculated column on an importrange shared sheet. I have tried the IFS and IF with AND and can get a timestamp with different variations of the formulas but the timestamp updates within a few minutes and doesn't stay as the original and I am thinking it is because of the fact the data is being imported rather than static direct data in the sheet. This needs to be very consistent as our payroll is going to be based on this timestamp. Also this data needs to be friendly inside of Data Studio as that is how we deliver these reports on an intranet site. **One of the variations of the formulas seems to give a weird 12/30/1899 **00:00:00** when all conditions are not met.** ***I have also tried the AppScript option and I feel like I'm in over my head there other than being able to change out the Sheet names and adjusting the column placement and I'm not opposed to going that route but I have limited understanding of AppScript/JavaScript. Great videos btw - you have got me out of some jams in the past several years. Super clear and very in-depth content for the common person such as myself lol.
@rosebellbicar11252 жыл бұрын
This is amazing, new subscriber here. Is it possible to add the end time if the person entered another data in the next row?
@les-fauxmonnayeurs98872 жыл бұрын
I added the function to calculate the Timestamp but in my Google Sheet but it disappear after a second. Have encountered this issue before? (I have the iterative calcualtions turned on and have to 1) update// for some reasons, nesting the =IF (IF ()) worked, but IFS still disappearing (I'm using Google Sheet)
@walterpaiva7194 жыл бұрын
As always, another awesome video!!! But where on earth do you learn it on the first place?
@ExcelGoogleSheets4 жыл бұрын
By watching my videos :))))) Thanks!
@SinYen982 жыл бұрын
Hi Thank you for your insightful video! May i know the timestamp, if i want it to be updated each time i changes another cell, is it possible in Google sheet with just formula? May I know how to achieve this if its possible? I managed to did on Excel file but Google sheet doesn't allow the formula to work 😭
@Allen-L-Canada Жыл бұрын
Thanks, what if I want the timestamp to show the last modified date and time, without having to delete the data first? Just override the data, then the timestamp is updated.
@daniellewilson68879 ай бұрын
Few questions about the timestamp formula: 1) I want it to continuously update if the cell has changed not just when it goes from blank to having text. I want timestamp to change each time text changes 2) I want my formula to look at the entire row for changes not just a single cell is that something i can do with this formula?
@MrRah6193 жыл бұрын
Awesome
@akyer80853 жыл бұрын
I want to create some sort of formula where by adding a certain amount of hours or days from today's date, the timer would count-down that tracks every minute real-time (if possible every second) that would then perform an action. Ex. Adding 2 days (48 hours) from Today, in which it'll begin a count-down timer from exactly 2 days, and when the timer hits 0, it would turn the cell red. How can you constantly update the spreadsheet so that the action can be taken once the timer hits 0?
@oleksiytanasiychuk35792 жыл бұрын
Hello there thank you very much. I need your help. I have an automatic flow that feeds the google sheet. I basically want to have the time stamp every time the file gets updated however every time it does, the time stamp deletes automatically the formula and if I try to put it again, it shows me something like 31/12/1889, Can anyone help me please? Thank you
@sergebrosseau9913 жыл бұрын
I'm looking for a way to have the formula applied to a whole column so that if a row is added anywhere I can put a timestamp without having to copy/paste the formula. So far when using an Array, and I update a cell in Column A, ALL of the timestamps in Column B get updated to be the exact same.=Arrayformula(IFS(A2:A ="";"";B2:B="";now();FALSE;B2:B)) The " ; " is where a comma goes because I'm using Sheets in French.
@est_ideya4 жыл бұрын
Very useful videos on your channel! It is a pity that there are no such videos in Russian. Tell me, please, how can I solve the problem of automatically copying data from a table in XLSX format to Google Sheets? Both tables are on Google disk. The data from the mobile application is periodically loaded into the XLSX table, it is necessary that this data is immediately loaded into Google Sheets. Thank!
@ExcelGoogleSheets4 жыл бұрын
This will require lots of API work. I guess as a workaround you could create a script with nodejs and setup a cron job on your computer to run it every X hours or minutes, assuming you are syncing that file to your hard drive. kzbin.info/www/bejne/gqiklqGLgLyqbsk
@charalamposkounes50713 жыл бұрын
@7:50
@fragrenceful3 жыл бұрын
I also got reference error in Google sheets, like array formula expanding and overwrite in cell a6 so how solve this issue because I have to show daily Live stocks LTP change Hoti hai to mera stock portfolio sheets balance hota rahe, please guide ASAP
@markokrstajic1013 жыл бұрын
Hi all, Can someone help me with this problem. I have timestamp script running in google sheet and it work fine when I manually change value in cell. But I can't find any solution how to have timestamp working when entry in trigger cell is some auto data from formula or such. Or maybe if someone have another solution. What I'm doing now is, from sheet1 with Query transfer data to sheet2 when some change is made. When new row is created in sheet2 I want to have date of entry for every row that is created in sheet2, and date need to be fix all the time.
@KuldeepSingh-pl7rv3 жыл бұрын
I am using If condition formula with now for date ref cause i have prepared an FMS sheet but the problem is i dont know why when I try to update or open sheet next day it all date returns to 30-sep-1989 by default. any solution
@deanpournaras74603 жыл бұрын
Great explanation! Found this because I was looking to populate Time Stamp and the person on our team who created the record in a Shared Google Sheet. Any advice on how to capture who created the Record?
@ExcelGoogleSheets3 жыл бұрын
You'll have to use installable trigger with a script and every person using it will need to authorize the script, otherwise google will not allow to get user's information. I'm not sure if you already know this, but Google Sheets has version history which logs all changes and users who made those changes.
@deanpournaras74603 жыл бұрын
@@ExcelGoogleSheets Thanks. I am aware of version history. It helps, but for each line created, I would like to know who created the record. I do not know scripts. Can you point me in the right direction?
@elwadmezoo3 жыл бұрын
if I set a formula to set today date if another is not black how can I make Today() formula to be unchangeable when I open the sheet on the next date
@KarolKarasiewicz8 ай бұрын
Sir, is there a way to do this using LAMBDA()?
@aaquibhasan80104 жыл бұрын
Thank you
@ExcelGoogleSheets4 жыл бұрын
Thanks!
@aaquibhasan80104 жыл бұрын
@@ExcelGoogleSheets I have been using this for time stamp =IF(N1,N1,IF(L1"",$A$1,"")) where a1 cell contains now () formula , but after data all collected sometimes date changes automatically in some of the cells , Tried everything but could not find out the reason
@mdsyahril7574 жыл бұрын
I need help! I am using a google form to conduct my check-in and check out for my guest. However, I want to make it neat as to link to the same person to check-in and check-out on the same row. By default, google excel will continue the list down for the 2 activities. The end state is to see my guest with their check-in timing and check-out timing in my record. Please advise.
@ExcelGoogleSheets4 жыл бұрын
Google Forms is what it is. If you need custom stuff you need to use a Web App connected to Google Sheets.
@MrBratz11072 жыл бұрын
how about for rows?
@drafttails76753 жыл бұрын
Hi there, Is there any way that you can help out with this time stamp. The problem is: - When clicking the check box with IN it stamps the time in "IN" and then in the end of the day when the individual goes again and uses the "Out". It doesn't happen straight away, meaning that when they stamp in at 09:00 AM and then stamp out at 04:00 PM the 04:00 PM goes into both boxes. Hope you can see what I can't see. drive.google.com/file/d/1eiy9AJz_zkgegKJn-VGOQ4hNbly3DQSJ/view?usp=sharing
@ahmedmagdy75954 жыл бұрын
Hi hero, I want to guide me if buliding web app by apps script and I want set time slots and schedule and if it booked ignore that to appear for another option. For example I have to set start time 10:00 AM end time 3:00 pm Except to receive an array with 15 minutes interval in dropdown 10:00, 10:15, 10:30 an so on If I selected let's say 11:00 am and set duration 2 hours Need to block from 11:00 unti 1:00 pm to show up for coming drop down. Wish to support me 🙏
@CJF204 жыл бұрын
Please tell me how to use 2 Cell in 1 timestamp? Please send your formula
@ajayshakti58183 жыл бұрын
Iterative calculation must be on for timestamp activation which should not be changed if re update in sheet.
@jeffbrylinski41473 жыл бұрын
mine is now showoing 12/30/1899 0:00:00. what did i do wrong
@pearldme74563 жыл бұрын
Actually, I have a problem with this as It changes time every single minute. If the sheet is open or close, It constantly changes time.
@steveroger28472 жыл бұрын
Sir, I did this for 2 columns [=ifs(and(B2="",C2=""),"",A2="",now(),true,A2)], A-column is the automatic time column, B & C are input. What happens, sometimes when I open the sheet again, some latest cells of the time column gets updated on the time I open. It doesn't happen every time, with every cell, but most of the times it happens when I reopen the sheet (basically, when I open it, during load, the latest 2-3 cells stays blank, so get updated after loading. Please help, solving this... Also happens in case of single column, I mean same with what's shown in this video.
@ExcelGoogleSheets2 жыл бұрын
Use this method instead kzbin.info/www/bejne/a2WblXdpnr2Yqsk
@Mary-fd8qn3 жыл бұрын
Thank you so much for this video. This is amazing! However, I am encountering issues doing this process. When creating and modifying the sheet, it works on the timestamping. However, when I closed the google sheet and reopen the file again or just refresh the file, the date becomes 30 December 1899 12:00 AM. I did exactly what was said in the video, I don't know what's wrong. Can anyone help me fix this?
@ExcelGoogleSheets3 жыл бұрын
Use this method instead kzbin.info/www/bejne/a2WblXdpnr2Yqsk
@Mary-fd8qn3 жыл бұрын
@@ExcelGoogleSheets Thank you! Thank you so much! :)
@elielaraujo4 жыл бұрын
When it is blank, ok it works. But note that when you UPDATE a cell that is already filled it doesnt work. In the video when the cell b2 is filled and then updated, the time stamp remains the same
@ExcelGoogleSheets4 жыл бұрын
Yes, that's how it's supposed to work. To get time last modified check out this video kzbin.info/www/bejne/a2WblXdpnr2Yqsk
@JeanMalissard4 жыл бұрын
@@ExcelGoogleSheets Any idea how to get the time stamp of the last update with formula ? (no script)
@manishsinghal18744 жыл бұрын
How can i use array formula in Google sheet
@ExcelGoogleSheets4 жыл бұрын
Watch this video kzbin.info/www/bejne/o2eQZ3-bbMx4prs
@donekalgarisurendra77172 жыл бұрын
Please let me know if there any formula!
@franciscoarocha1503 жыл бұрын
Does anybody know how to add a timestamp when a row is inserted (Auto timestamp on the inserted row)?? I'm going crazy, please help!
@shouravsts3 жыл бұрын
Did you find it bro?
@Puner543 жыл бұрын
Why doesn't Sheets have a Function for Time that does this?
@sudaesh3 жыл бұрын
There should be a time stamp formula
@wanyeglennasaah25582 жыл бұрын
WHAT IF I WANT THE TIME STAMP ON GOOGLE SHEET TO CHANGE AS I CHANGE THE COLOR OF THE CELL
@wanyeglennasaah25582 жыл бұрын
PLEASE HELP OUT
@patricioperez19854 жыл бұрын
can this be used for sharing the file? do the user would need to set iterations
@cvMouse3 жыл бұрын
Why is it that every time i open the document it recalculatea and changes the timestamp with the new now date?
@ExcelGoogleSheets3 жыл бұрын
If it doesn't work for you, try this instead kzbin.info/www/bejne/a2WblXdpnr2Yqsk
@shinopaints60943 жыл бұрын
Sir jab Sheet Waps se Open krte tho date change ho jati hai
@lucascarvalho2154 жыл бұрын
I did exactly the same thing, but for some reason it didn't work. Edit.: I changed the location and it worked! I'm Brazilian. Edit.: I turn back to the original location and the form turns to this: =IFS(H2="";"";A2="";AGORA();VERDADEIRO;A2).
@ExcelGoogleSheets4 жыл бұрын
kzbin.info/www/bejne/opvZc2WXp6imhLM
@lucascarvalho2154 жыл бұрын
@@ExcelGoogleSheets thank you! I really appreciate your work. As we say in Brasil, tu é foda!
@donekalgarisurendra77172 жыл бұрын
Hi sir , can i get editer ma id alos?
@donekalgarisurendra77172 жыл бұрын
User Mail id
@mohamed.montaser4 жыл бұрын
no matter what, the timestamp column will get recalculated all over again
@ExcelGoogleSheets4 жыл бұрын
Correct. If you don't like that use this kzbin.info/www/bejne/a2WblXdpnr2Yqsk