How to Format Date in Google Sheets (3 Easy Ways)

Working with dates is one of the more common tasks available for any spreadsheet user. However, many users of varying expertise still have certain confusion on how to use and format date in Google Sheets. So today, we will look at a few ways to do just that.

Let’s get started!

3 Ways to Format Date in Google Sheets

1. Change Date Format from the Spreadsheet Settings in Google Sheets

This method works by permanently updating the settings of Google Sheets. Note that by saying permanently we don’t mean unchangeable, but a long-term change until you want to update the settings again.

By default, Google Sheets will have the date format set according to your locale. We can change this locale anytime by using the Spreadsheet Settings option.

Simply navigate to the File tab and move down to find the Settings option.

File > Settings

navigating to settings to format date in google sheets

In the Settings for this spreadsheet window, you will find the locale section. By default, it is set to the United States, meaning the date format is in MM/DD/YYYY.

locale settings set to united states by default

Simply change the locale to what you need (we have changed it to the United Kingdom), then click Save and reload.

locale settings changed to format date in google sheets

This will reload the spreadsheet on your browser and update the date format (it was DD/MM/YYYY for us). Note that existing dates within the spreadsheet will not change but will be recognized in the new format.

You can see the changes in the general function tabs:

Before: The default date format

Before: The default date format

After: The new date format

After: The new date format

2. Create a Custom Date Format

Many professional workspaces use their own version of date formats for their reports. These formats cannot be achieved by just changing the locale of your spreadsheet as we have seen in the previous section.

However, Google Sheets does provide us with a way with which we can customize the date format of the current spreadsheet. That is by using the Custom date and time option.

We can find this option by navigating to the Format tab and then under the Number option.

Format > Number > Custom date and time

navigating to custom date and time from the format tab

Here in the Custom date and time formats window, we can either choose one of the multitudes of pre-existing date formats available:

pre-existing date formats available in google sheets

Or create our own:

we can create our own custom date format

As an example. we will convert the DD/MM/YYYY format to DD-Month Name-YYYY. So, select the cell(s) that contain the date and make the following changes in our customization window:

1) Click on the Month tab to view the various options.

click the month tab

2) Select the Month as full name option.

the month format options available

3) Replace the “/” delimiters with “-”.

updating the delimiters

4) Click on Apply to see the results:

how to format date in google sheets using custom date and time option

Extra Tip:

You can add extra fields to your custom date by clicking on the drop-down button on the right of the panel.

we have many more fields that can be added to the date format in google sheets

3. Use TEXT Function to Convert Date Format

We all know that Google Sheets stores dates as numerical values and these values are represented as a date format, MM/DD/YYYY as default.

However, thanks to Google Sheets’ TEXT function, we can convert this numerical value in date format to something else.

Here are some examples of how we can format the default date using the TEXT function in Google Sheets:

how to format date in google sheets using the text function

But one very important point you must understand is that the converted date is no longer a numerical value! This is an obvious giveaway since we used the TEXT function and also that the values in the converted cells are left-aligned.

Alternative: Using the QUERY function

We can also apply a query using the QUERY function to act similarly to the TEXT function. The advantage of QUERY lies in its power and versatility as a function.

The formula:

=QUERY(B3:B7,”select * format B ‘dddd, dd-mm-yyyy'”)

how to format date in google sheets using the query function

Like TEXT, you can define the format of the date that is to be presented with the QUERY function.

Final Words

That concludes all the ways we can format a date in Google Sheets. We hope that the methods we have described are among the ones that you were looking for and that it comes in handy for your spreadsheet tasks.

Please feel free to leave any queries or advice you might have in the comments section below.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for officewheel.com specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo