How to change the date format in Google Sheets (Easy methods)

The date plays a huge in saving data. So, recording data with a date in Google Sheets is important. At this point, Google Sheets date formulas and other menu extensions come to your rescue. If you want to learn how to put a date in Google Sheets or change it, keep reading.

In this article, you will learn how to change the date format in Google Sheets with the following easy methods. If you are looking for adequate Google Sheets time functions with excellent explanations, you will get what you want because we have gathered a complete guideway to change the date format in Google Sheets and more!

What is the default date format in Google Sheets?

The default date format in Google Sheets is the format that Google Sheets determines according to your location. For example, if you are a user from the UK, the default date format will be DD/MM/YYYY. However, if you are a user from North America, the default format will be MM/DD/YYYY. 

In other words, the location that you exist in defines the default date format. As you wish, it is possible to change the date format. If you want to change the format but do not know how to, no worries because in our article, you will learn the process step by step. 

How can I change the date format in Google Sheets?

The main question of our article is, “How can I change the date format in Google Sheets?” and it is possible to do it in various ways. You can change the format on the menu extensions or by using some functions in Google Sheets. So, you have 5 options for custom formatting, and here are them:

Method 1: Date format in the format menu

If you do not want to use any date function in Google Sheets, you can simply use this method. This method is pretty easy to use because it requires no formula. If you need to change your date format without applying a function, here are the steps:

1. Select the data you want to change.

2. Choose Format from the upper left corner. Then click the 123 - Number button from the extension.

Opening the number section under format in Google Sheets

3. Choose any format you want from date time to duration. 

Number options for date, time, date time, and duration in Google Sheets

  💡 You can also find the 123 Number button from the toolbar easily.   

Method 2: DATEVALUE function

Using the DATEVALUE function method is a useful Google Sheets date function. The formula creates a date you want with the selected format. The DATEVALUE function is a handy Google Sheets date format formula for you to use in dates. Check the steps given below:

1. Select the cells you want to apply the formula.

2. Type =DATEVALUE(value) and press enter.

3. For example, if you type =DATEVALUE(B2), which consists of  “10-10-2023”, you will get 45209, which represents the serial number of the cell B2. 

💡 If the DATEVALUE function is not working properly, be sure that your locale and time zone are set correctly. 

Method 3: TEXT function

The TEXT formula transforms numbers into texts, including currencies, dates, decimals, or other formats. It is mainly used in dates. If you need such a formula, follow the steps given below:

1. Click on any blank cell.

2. =TEXT(A2, "MMM-DD") for a cell that contains “December 1, 2023”; it will give you only the day, which is Dec-01. 

3. If you type =TEXT(A2, "dd mmm yy"), it will give you the month in text and the other details in numbers. Just drag the little ball on the bottom right corner to apply this formula to other cells.

💡The TEXT function is great for date and time formatting for a specific format. Here are other formatting abbreviations you can use.

‣ d: the day expressed as one or two digits (e.g. 5)

‣ dd: the day expressed as two digits (e.g. 15)

‣ ddd: short name of the day of the week (e.g. Mon)

‣ dddd: full name of the day of the week (e.g Monday)

‣ m: name of the month as one two digits (e.g. 9)

‣ mm: name of the month as two digits (e.g. 10)

‣ mmm: short name of the month (e.g. Dec)

‣ mmmm: full name of the month (e.g. December)

‣ yy: year as last two digits (e.g. 23)

‣ yyyy: year as four digits (e.g. 2023)

‣ h/hh: AM/PM hour clock (e.g. 1:05 PM)

‣ h/HH: 24-hour clock (e.g. 13:05)

Method 4: QUERY function

Formatting the date, time, and number columns in Google Sheets is made easier for users using the Query function.  The QUERY function returns the long dates into the ideal date format you wish. If you are curious about the function, have a look at the steps:

1. Choose the data you want to custom. (e.g., from cell A2 to A11)

2. Decide on how you want to custom date and time.

3. Type =QUERY(value1:value2,” SELECT * FORMAT A ‘DD-MMM-YYYY'”) and arrange the formula in Google Sheets according to your needs. Press the enter key to see the result. 

4. For example, if you type =QUERY(A2:A11, "SELECT * FORMAT A 'DD-MMM' "), you will see that the format will change from “November 2, 2023” to “02-Nov”. 

💡 You can change the day-month-year by checking the list we shared with you in the TEXT function section. 

Method 5: Change the regional format

This method is another one that requires no formal to apply. As soon as you follow the basic instructions given below, you can change the format to another locale is really easy. To change the local from the toolbar, have a look at these steps:

1. Click File from the toolbar menu and click Settings.

Opening the file settings

2. As you click, you will see both Locale and Time Zone. 

Finding the Locale and Time zone from settings

3. You can choose what you need, and after saving it, the date, year, month, and day will be according to your saved settings.  

Frequently asked questions about the date format in Google Sheets

Even though we have explained how to change the Google Sheets format date as text or vice versa, you might still have questions about the issue. To help you find out more and find the answers to your questions, look at the frequently asked questions about the date format in Google Sheets given below:

In short

To wrap it all up, Google Sheets is an excellent tool for both individuals and businesses to save data. While saving the data, there might be moments when the date matters. At this point, it is essential to know the practical usage of Google Sheets formulas. You can learn more from our article about the best 20+ Google Sheets formulas to make your work easier. In our article, we aimed to guide you in changing the date format in Google Sheets with easy methods.