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 00:19 or task management, we need dates.

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 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  00:57 that I set the locale, then every date setting will change for that.

So all this stuff, right? So how do we go back and change any date formats? 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. 01:24 So let's get into it because it is a little bit daunting. know which format to set. 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. 01:49 And this drop down menu will have the two date formats I want.

So, it could be month, month, slash, day, two days, slash, year, year, year, or the days dd, slash, mm, slash, four years. 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. 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 . 02:25 So we're going to go up to extensions, Apps Script , and write a little bit of code.

Here we're going to write the function updateDateFormat. 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 . 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. 03:05 So this will be that settings. So it'll be actually ss.getSheetByName.settings.

getRange , a2, getValue . 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. 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. 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. 03:40 Our column is going to be one because it's in the A column.

Again, basically we're based on where you have your data , you're going to change this number . 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 . 04:06 That's going to be it. So here, now we're going to write our for loop.

And so our for loop needs three things. 04:12 Our for loop needs a, where to start, where to go until, and then how are we going to iterate it. 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. 04:43 So how do we iterate it? Row plus plus.

It's just going to add one to the row every single time. 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. 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. 05:31 So we're going to save this all.

Now, if I go back to my settings check, it's day first, then month, then year. 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 . 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. 05:57 And let's see if it works. If we get any errors or anything, they'll come up right away.

And if it works, now our format of date. 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. 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. 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. 06:40 And the first item, the name, is update date format .

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. 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 . 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 . 07:25 I think we have to authorize it and again because we added the UI.

Month, Day, now they are all changed. 07:40 Great. So, I'm going to show you another way to automate this that I think is much easier. Much more elegant. 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. 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. 08:03 So we can use onEdit.

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. 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 . 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. 08:48 app.getActiveSheet.getName. There we go.

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

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. 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. 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. 10:23 There you go. That's it.

That's how to change your date format automatically across your sheet or across a range of cells or a column, anything like that. 10:30 Hopefully that's helped you and hopefully it'll make your spreadsheets better.</div>