How to change the date format in Google Sheets

In Google Sheets, the user is presented with numerous figures that can help them organise that spreadsheet, whilst also being offered the opportunity to alter the layout of these figures to better refine their work. For example, you are shown the last time of edit, and are given the option to change how numbers such as times and percentages are presented.

Also within Google Sheets, though, the user is given the chance to change the date format. American by default, that format may prove confusing to users not from the States, which is understandable. Thankfully, this is an issue that’s quick to resolve and easy to do. In order to edit the date format on Google Sheets, the user must do so through the ‘More Formats’ option.

From here, the blog will present you with a step-by-step walkthrough on how to edit your date format on Google Sheets. If you find yourself needing some more details than what is offered by this, then fear not. Following the steps will be a more in-depth, detailed breakdown on the process that should successfully guide you through completing the task.

Step by step: How to change the date format in Google Sheets

  • Open your Google Sheets spreadsheet.
  • Click ‘Format’ on the top navigation bar.
  • In the dropdown menu, click ‘Number’.
  • From here, another menu will appear. At the bottom will be ‘More formats’. Click that.
  • Here, click ‘More date and time formats’.
  • Select the format you feel is most appropriate and best suited to you.
  • OR
  • Remove the pre-sets in the bar by deleting them.
  • Click the downward facing arrow.
  • Choose the order of your date.

We’ve also created a video that walks you through changing the date format in Google Sheets. You can watch it here:

Above is the step-by-step process of how to adjust the date format of your Google Sheets document. As was aforementioned though, below will be a more detailed explanation of the process, should you need it.

So, your first step will be to open your Google Sheets document. When you have done this, you will want to look to the top of the screen, at the navigation bar of the spreadsheet. Looking here, click Format, the fourth tab along. Below is a screenshot, with the Format tab highlighted by the red rectangle.

Once you have clicked format, you will be greeted by a dropdown menu, listing many options of the format that can be edited. With wanting to change your date format, you will want to click on the Number option, the second one down the list. It is emphasised below, again, by the red rectangle.

Clicking Number will once more present another list for you to choose from. Of all the new options listed, you will want to choose More Formats, which is located at the bottom of the newly shown array of choices. Scroll to the bottom, and you’ll see the option at the foot of the list.

From here, you will be presented with three titles that fall under the ‘More Formats’ subsection of the format options. To alter your date layout, you will want to click the middle one of these three, the one named ‘More date and time formats’. Once more, it has been emboldened below.

After clicking this option, a new menu will open on your screen. It will display the current way in which your date and time are shown, whilst also presenting you with a plethora of ways in which you can edit that format. It should look as thus:

From here, you will be able to customise your date format and edit how it will look, with each aspect down to your choosing. You can choose a preset that offers a different order of the date/month/year, or choose to have the date presented in a more analogue fashion, listing the day of the week alongside the date/month/year.

Not only that, but you can edit how the date, month or year will be displayed within your date. So, if you click on any of the three sections, you will be presented with a menu that offers you a few variations on layout. Of course, this is an added detail of the walkthrough, and is in no way mandatory to do in order to progress.

However, if you clicked on this article, you would have done so wanting to reformat your date into the British layout: dd/mm/yyyy. You’ll notice as you’re scrolling through that there is no preset British date layout. Why? Well, with it being an American application, it makes a lot of sense, but I digress.

There may be no preset, but this won’t stop you from entering a British style into your date. Look to the bar showing the order of your date. It should look something like this:

Clicking on any of these three boxes, as briefly touched on above, will give you the opportunity to make slight alterations to them. Also though, is the option to delete the box from the line.

In order to manually rearrange your date format, you will need to delete the contents of that top line. After it is empty, you will be able to re-enter them in an order that best suits you.

To do this, click the small, downward pointing arrow at the end of the box. From here, a menu will open, and you can click the aspects you want entered into your layout in an order that best suits you. 

So, to get the British date format, click ‘Date’ first, followed by ‘Month’, then ‘Year’.

In the screenshot below, a red arrow points to the arrow you need to click to open the menu.

After you have entered your date, it should look something like this:

When you’re happy with your format, simply click Apply, the big green box next to your date bar which is visible above, and throughout the screenshots in the article. You have now successfully restructured your date format in Google Sheets!

Thanks for taking the time to read this blog, I hope it was as helpful as you wanted it to be! As always, if you come across any issues, please don’t hesitate to get into contact with our team.

Michael Fontana

Michael Fontana has been the managing director of telecoms and MSP Optionbox for over a decade and has worked in various telecommunication roles over the last 20 years. Michael has been involved in many exciting projects, such as co-founding telecoms and IT businesses and has now built up a team of more than 15 staff, serving over 300 clients nationwide. With a wealth of experience in IT and a passion for technology, Michael is now helping BTP to produce the highest quality guides on the internet. You can connect with Michael on LinkedIn.

Recent Posts