This post is going to show you how to convert dates into the MM/DD/YYYY format in Google Sheets.

Dates are an integral part of any data.

They provide valuable dimensions that are useful for understanding the changes in variables over time.

Working with dates can get tricky. To get the most out of using dates in your analysis, you must get them in the appropriate format.

Dates come in various formats and having the date values in your dataset formatted properly can be the difference between having a reliable analysis report or not.

Why are my Dates in DD/MM/YYYY Format?

The format of a date describes how the day, month, and year components are arranged when writing a date. This sequence or arrangement is essential when working with dates.

Date formats fall into one of these three types.

1. DD/MM/YYYY as in 31/12/2022
2. MM/DD/YYYY as in 12/31/2022
3. YYYY/MM/DD as in 2022/12/31

The date format type you use will depend on your region or locality.

Most tools use the DD/MM/YYYY date format as default. This is because almost every country/region in the world uses this date format.

That means the dates in your Google Sheet will also use the DD/MM/YYYY format.

However, the American date is in the format MM/DD/YYYY. When you get Google Sheets from other regions, the dates will most likely have the DD/MM/YYYY format.

📝 Note: Check out this post if you need to change your dates to the DD/MM/YYYY format instead.

How to Set the Default Date Format to MM/DD/YYYY

Changing the default date format in Google Sheets is very easy. You just need to change your Locale settings.

1. Go to the File menu.
2. Select Settings.

3. Click on the Locale option inside the Settings for this spreadsheet dialogue box,
4. Select the United States from the list of countries in the dropdown menu.
5. Click on the Save and reload button.

By the time your spreadsheet reloads, the date format will be in the style MM/DD/YYYY.

Other Ways You Can Change the Date Format

If you don’t want to change the locale setting but only the date format in your spreadsheet, there are other ways you can do it.

You can use the TEXT function, the QUERY function, or the Custom date and time format.

Display Dates as MM/DD/YYYY using the TEXT Function

= TEXT ( number, format )

You can use the TEXT function to apply formatting to cell contents.

= TEXT ( B1, "mm/dd/yyyy" )

The dates have been formatted using the above syntax. Note that the formatting instruction must be enclosed in quotation marks.

⚠️ Warning: The TEXT function will return dates as string values, meaning you cannot use the dates in further mathematical operations.

Display Dates as MM/DD/YYYY using the QUERY Function

= QUERY ( data, query, [headers] )

The QUERY function can do many operations with your data, including applying formatting to cell contents.

It returns the same results as the TEXT function with one slight difference, the dates remain as numeric values.

You can check out the full guide to the QUERY function for more details.

= QUERY ( B1:B6, "select * format B 'mm/dd/yyyy'")

The QUERY function is an array function, which means when you enter it in the top column, the other cells autofill with the formula result.

Display Dates as MM/DD/YYYY using a Custom Date and Time Format

The Custom date and time feature allows you to pass custom date formatting to a cell.

Follow these steps to add the custom format.

1. Select the cells you want to format.
2. Go to the Format menu,
3. Select the Numbers option, then select the Custom date and time format option.

4. Use the dropdown arrow to select the date components in the order you want them to appear.
5. Click on the Apply button.

You can also look through the preset date formats from the options available to see if what you want is there.

💡 Tip: Remember to add a delimiter when creating the formatting rules so the date components are easier to recognize.

Your dates should now be in the MM/DD/YYYY format!

Conclusions

Unfortunately, different regions use different date formats and you may come across the need to convert dates into another format.

You should always have your dates in the appropriate format for your region. This will prevent interpretation errors when users are reading your date values.

You can change the default date format in your spreadsheet by changing the Locale setting, or you can use either the TEXT function, QUERY function, or the Custom date and time feature to change only selected dates in your workbook.

Have you ever encountered dates in the wrong format for your region? Did you know how to convert them into the correct format? Let me know in the comments below!