How To Change Date Format Automatically

<div>00:00 I think this is going to be a very interesting video because it is something that I ran into myself when I'm creating Google Sheets either to sell or even give away, and we have date formats in them, and we're working with date formats in things like CRMs, with, uh, any time we're doing project management<br>00:19 or task management, we need dates. Google Sheets Interface Changes How To Automatically Add Dates in Google Sheets

Uh, even personal productivity sheets that I make myself, I need dates, and when I give them to someone else, for free, or even if I sell these sheets, uh, these date format sheets, end up changing, and we have something in file, uh, settings, we have this locale , and<br>00:38 this locale affects the formatting details, such as functions , dates, and currency, but, the thing is, that if I switch this to, say, a European country right now, or a country that uses a different date, my past cells , or any information or data that I have set, will not change, but from the moment&nbsp;<br>00:57 that I set the locale, then every date setting will change for that. Google Sheets Locales. What are they? Change Country in Google Sheets

So all this stuff, right? So how do we go back and change any date formats?<br>01:09 but show both date formats whenever someone from that date format wants to use it? There's two ways we can automate this.<br>01:24 So let's get into it because it is a little bit daunting. know which format to set.<br>01:38 So I'm going to create a new sheet called settings. I'm going to call this date format in A1. And in A2, I'm going to insert a drop down menu.<br>01:49 And this drop down menu will have the two date formats I want. Google Sheets Interface Changes Autofill Today's Date When Cell Edited

So, it could be month, month, slash, day, two days, slash, year, year, year, or the days dd, slash, mm, slash, four years.<br>02:05 And I'm going to hit done. And here, I have have either date format . Let's make that a little bit bigger so you can see it.<br>02:13 So here we have a drop down menu. We want to set the date format for all of our data . So for our test here, I have column A, but you can set this and I'll show you how to set this for range of data .<br>02:25 So we're going to go up to extensions, Apps Script , and write a little bit of code. Every Locale Listed in Google Sheets and a Tool to Save Your Sanity Autofill Today's Date When Cell Edited

Here we're going to write the function updateDateFormat.<br>02:39 We're going to need a function . We're going variables, variable ss=" spreadsheetApp .getActiveSpreadsheet". We're going to need where we're going to actually change our data or date format .<br>02:52 So this is the sheet ss.getSheetByName. That's right. And we're going to use data . That's the name of the sheet. We need the actual date format that we're going to change to.<br>03:05 So this will be that settings. So it'll be actually ss.getSheetByName.settings. Getting Started Coding in Apps Script Get new Date TWO SHEET TIMEZONES

getRange , a2, getValue .<br>03:18 So this will just get us whatever is in that a2 cell, that date format that we're going to have to change to.<br>03:24 Now we're going to need to do a for loop. This for loop is going to need a few items, so we're going to need to know our start row.<br>03:35 So that's going to be two because it's the second row for us if your data is different, use different things.<br>03:40 Our column is going to be one because it's in the A column. Copy Date to Next Cell Automatically

Again, basically we're based on where you have your data , you're going to change this number .<br>03:48 And we're going to need to know what is the last row we want to change. So our last row is equal to data . getLastRow So we don't have to do everything, even setting date formats for blank cells .<br>04:06 That's going to be it. So here, now we're going to write our for loop. 80 Years In 1 Spreadsheet URL Link to Last Row

And so our for loop needs three things.<br>04:12 Our for loop needs a, where to start, where to go until, and then how are we going to iterate it.<br>04:19 So our variable row equals start row, semicolon, row is less than or equal to last row. So we're going to go until this row keeps iterating one up until it's greater than, it's going to still continue as it's less than or equal to the last row.<br>04:43 So how do we iterate it? Row plus plus. Use Row() For Math You Need to Iterate Count Color by Row

It's just going to add one to the row every single time.<br>04:48 Add a, curly brackets here, and we need to know where we're setting, so we need a cell, data . getRange , row, comma, column.<br>05:01 Our row is going to be this. Iterative here, and our column is going to be set right there, one. Well, we need cell equals data . getRange , and now let's set that date format , cell. getRange . setNumberFormat, and use the variable date format that we're getting from our variable here as the date format.<br>05:31 So we're going to save this all. Writer Better Prompts Copy Date to Next Cell Automatically

Now, if I go back to my settings check, it's day first, then month, then year.<br>05:39 Right now, all of my dates are month, then day, then year. So let's see if this works. Before we create any automations, let's just go in our Apps Script and click run for update date format .<br>05:51 We're going to have to review our permissions, authorize it if need be. This is just the very first time we run it.<br>05:57 And let's see if it works. If we get any errors or anything, they'll come up right away. What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users How To Automatically Add Dates in Google Sheets

And if it works, now our format of date.<br>06:08 Our dates are the day first, then the month, then the year. Great. So how do we automate this? Again, I'm going to show you two ways to automate it.<br>06:16 Go to bettorsheets.co slash snippets. And you can get an unopened menu here. We're going to use this one. This function on open up here.<br>06:27 Just copy and paste it from bettorsheets.co slash snippets. And we're going to call this automations. And we just need to put this function as the second item here.<br>06:40 And the first item, the name, is update date format . Google Sheets Interface Changes Americano Tournament Template With Four Automations How To Automatically Add Dates in Google Sheets

We're going to delete the second one, actually. Once it's saved, the one thing we have to do is close Apps Script and refresh our sheet or close it.<br>06:56 Close it and open our sheet. Once we do that, I think, next to this Help menu , we are going to get, let's see, update date format .<br>07:09 So, we can, there it is. So, next to the Help menu , here is our Automations and Update Date Format . So, let's go back to our Settings, A2, change this back to Month, Day, and now click on Automations Update Date Format .<br>07:25 I think we have to authorize it and again because we added the UI. Automate Row Deletion in Google Sheets with Apps Script Automatic Calculations Autofill Date on Edit in Multiple Columns

Month, Day, now they are all changed.<br>07:40 Great. So, I'm going to show you another way to automate this that I think is much easier. Much more elegant.<br>07:45 We don't need to add this, we don't need to add this menu, but I think it's a nice thing to add as well.<br>07:52 But let's go back to our Apps Script . Now, we need to run this update date format when we actually change our settings.<br>08:03 So we can use onEdit. Google Sheets Interface Changes Add A Timestamp to Task Lists (without Now Formula) Autofill Date on Edit in Multiple Columns

This function onEdit is a built-in function with an event e that says when this event happens, this e, there's some interesting special things we can know, what row we're on, what sheet we're on, all this stuff.<br>08:16 And we can say, basically, if we're on the right sheet, if we're in the right row, if we're in the right column, run this function .<br>08:22 So we can say, variable row equals e. range .getRow, variable call, for short for column, equals e.getRow. e. range .getColumn, capital C, and variable sheet equals e.<br>08:48 app.getActiveSheet.getName. There we go. Spreadsheet Automation 101 Lesson 2: onEdit() Trigger How To Create a Stop Watch in Google Sheets When Urgent, Move to Top of Google Sheet Automation is not Magic

So now we say if sheet is equal to settings, and we're going to use double ampersand, row equal, is equal to two, and ampersand call is equal to one, two equal signs, basically this is a column, two rows.<br>09:20 I'm going to put a, uh, curly brackets here, and then we're going to run this update, date function , need the parentheses.<br>09:29 So basically, only if we're editing that particular cell, on that particular sheet, will we update the date format . So let's save this all, go back to our page, and see what is it. Google Sheets Interface Changes All Sheets Update Status Change Date Automatically

It is month, then day, then year, so let's change this to day, month, then year, and go back, and it is now changed.<br>09:56 Without having to go up to automations, without a secondary action, our users can just change this date format in A2, and it will automatically change the date format for the entire sheet, or wherever you want your pointing to.<br>10:09 We can add more, uh, basically places to update with just adding more of these variable start rows, these four loops with the particular information we need.<br>10:23 There you go. That's it. Google Sheets Interface Changes Autofill Today's Date When Cell Edited

That's how to change your date format automatically across your sheet or across a range of cells or a column, anything like that.<br>10:30 Hopefully that's helped you and hopefully it'll make your spreadsheets better.</div> Google Sheets Interface Changes Copy Date to Next Cell Automatically Autofill Today's Date When Cell Edited