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.
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 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');
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!
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!!!
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?
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!
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?
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.
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.
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.
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 😭
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
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?
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)
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.
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.
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?
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
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!
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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-LwAenUKWw8g.html
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
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
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.
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?
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.
@@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?
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.
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 🙏
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.
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?
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
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
@@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
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).