How to change Google Sheets date format to dd/mm/yyyy?

Have you ever found yourself staring at a Google Sheet filled with dates that just don’t look right to you?

Maybe you’re more comfortable with the day coming before the month, but your spreadsheet is stubbornly displaying everything in the mm/dd/yyyy format.

It’s a small thing, but getting dates in the format you’re used to can make reading and entering data a lot easier.

In this post, we’re going to walk through how to change the date format in Google Sheets to dd/mm/yyyy, so you can get your dates looking exactly how you want them.

How to change Google Sheets date format to dd/mm/yyyy

Step 1: Open Your Google Sheet

First things first, you’ll need to open the Google Sheet that contains the dates you want to reformat. If you haven’t created a sheet yet, now’s the time to do so.

Step 2: Highlight the Date Cells

Click and drag your mouse over the cells that contain the dates you want to change. If all the dates are in a column, you can click on the letter at the top of the column to highlight the whole thing.

Step 3: Open the Format Menu

With your cells still highlighted, look for the “Format” option on the top menu. Click on it, and a dropdown menu will appear.

Step 4: Find the Date Formatting Options

In the dropdown menu, hover over “Number” to open another side menu. Here, you’ll see a bunch of different formatting options, including one for “Date.”

However, since we want a specific format (dd/mm/yyyy), we’re going to skip this option and go straight to “Custom number format” at the bottom of the list.

Step 5: Enter Your Custom Date Format

After clicking on “Custom number format,” a small window will pop up. Here, you can enter the exact format you want for your dates. Type in “dd/mm/yyyy” and then click on “Apply.”

And that’s it! Your selected cells should now display dates in the day-month-year format.

But what if you’re dealing with a spreadsheet that uses a different system for dates, like text format or another country’s standard format? Don’t worry; we’ve got you covered.

Dealing with Dates in Text Format

If your dates are entered as text (meaning they don’t change format when you try the steps above), you’ll need to convert them into a date format first. You can do this by using the DATEVALUE function.

For example, if your text date is in cell A1, you would enter =DATEVALUE(A1) into another cell.

This will convert your text date into a serial number that represents the date in Google Sheets. From there, you can apply the “dd/mm/yyyy” custom format to this new cell.

You can find the full tutorial of this here.

Handling Different Country Formats

Sometimes, you might be working with a sheet that was created in a country with a different standard date format. In this case, changing the format might not be enough if Google Sheets isn’t recognizing your dates correctly.

This usually happens when the day and month could be interchangeable (like 04/05/2023 could be April 5th or May 4th depending on where you are).

If Google Sheets is interpreting your dates in a way you don’t want, you may need to use a combination of the SPLIT and DATE functions to rearrange the components of your dates into the correct order before applying the “dd/mm/yyyy” format.

Final Verdict of How to change Google Sheets date format to dd/mm/yyyy

Customizing your date format in Google Sheets to dd/mm/yyyy is a simple process that can make your data easier to work with, especially if this is the date format you’re most familiar with.

Whether you’re dealing with straightforward date formats or more complex scenarios involving text or international formats, Google Sheets provides the tools you need to ensure your dates are displayed exactly how you want them.

With these tips in hand, gone are the days of confusion over whether 02/03 means February 3rd or March 2nd. Happy formatting!

FAQ: Changing Google Sheets Date Format to dd/mm/yyyy

1. What if the “Custom number format” option doesn’t show up for me?

If you can’t find the “Custom number format” option, make sure you’re using Google Sheets in a web browser.

This option might not be available in mobile apps or older versions of Sheets. If you’re on a desktop and still can’t see it, try refreshing the page or checking for updates to your browser.

2. Can I apply the dd/mm/yyyy format to an entire sheet at once?

Yes, you can apply the format to an entire sheet. To do this, click on the square between the row numbers and column letters (the top-left corner of your sheet) to select everything.

Then, follow the steps to apply the “Custom number format.” Keep in mind that this will change the format of all numbers in your sheet, so use this feature carefully.

3. What should I do if my dates are in a different language?

Google Sheets typically recognizes dates in the language set for your Google account or the sheet itself.

If your dates are entered in a different language and aren’t being recognized, you might need to change your Google account’s language settings or use the GOOGLETRANSLATE function to translate the dates into English before converting them with DATEVALUE.

4. How can I revert back to the original date format if I don’t like the new one?

To revert back to the original format, simply select the cells again, go to “Format” > “Number,” and choose the date format that was originally used.

If you’re not sure what that was, selecting “Automatic” will let Google Sheets decide, which usually brings it back to the default format based on your locale.

5. Will changing the date format affect how formulas work with these dates?

Changing the display format of dates does not affect how formulas work with them.

Dates are stored as serial numbers in Google Sheets, and changing their format only affects how they are displayed, not how they are calculated in formulas.

So, you can change date formats without worrying about impacting calculations.