Dates are an inevitable part of Google Sheets. And like many other concepts of spreadsheets, they require a bit of learning.
In this tutorial, you will find out how Google stores dates and how you can format them for your better convenience. Some date formats are offered to you by spreadsheets while others should be created from scratch. There are even a couple of handy functions for the task.
I also describe a couple of ways of how to convert your dates to numbers and text if necessary.
First things first: before any activities related to dates in spreadsheets, it's crucial to understand the core principles of how dates work.
For its internal database, Google Sheets stores all dates as integer numbers. Not sequences of a day, month, and year as we got used to seeing, but simple integers:
Unlike Excel that cannot store dates as negative numbers, in Google, for dates prior to December 31, 1899, the numbers will be negative:
Regardless of how Google Sheets formats dates for you to see in cells, spreadsheets always store them as integers. It's an automatic Google Sheets date format that helps to treat dates correctly.
Tip. The same goes for time units – they are merely decimals for your table:
A date paired with time is kept as an integer with decimal places:
Another important thing to keep in mind is your spreadsheet locale.
The locale is what presets your Google Sheets date format based on your region. Thus, if you're currently in the US, 06-Aug-2019 will be put as 8/6/2019 in your sheet, while for the UK it'll be 6/8/2019.
To ensure the correct calculations, it's vital to have the correct locale set, especially if the file was created in another country:
Tip. As a bonus, you can also specify your time zone here to record your file history in it.
Note. The locale doesn't change the language of your Sheets. However, the date formatting will be applied to the entire spreadsheet. Everyone who works with it will see the changes, no matter their place on the globe.
If dates in your tables are formatted inconsistently or all you can see is strange sets of numbers instead, don't panic. You simply need to change the date format in your Google Sheets using built-in instruments.
The integers successfully turn into the format that you'll recognize at a glance. These are default Google Sheets date formats:
Tip. You can find the same formats if you click on the 123 icon on the spreadsheet toolbar:
If you don't like how Google Sheets formats dates by default, I won't blame you. Luckily, there's plenty of room to improvise thanks to custom date formats.
You can access them from the same Google Sheets menu: Format > Number > More formats > More date and time formats:
You will see the window with lots of different custom date formats available. Whichever one you chose and apply, your dates will look the same:
If you're still not happy with the appearance of your dates, you can tailor your own custom date format:
Repeat till all necessary units are added (no worries, you'll be able to add or remove them later):
Here's what I can choose for Day:
This way, you can edit all values, insert additional and delete obsolete ones. You are free to separate the units with various characters including commas, slashes, and dashes.
Here's what format I've created and how my dates look now:
There's one more way to change date format in Google Sheets – with a formula, of course. Since this is not the first time of me showing QUERY to you, I'm starting to think of it as a real cure-all for spreadsheets. :)
I have an example table where I track the shipment of a few orders:
I want to change the date format in column B. Here's my QUERY formula:
=QUERY(A1:C7,"select * format B 'd-mmm-yy (ddd)'")
The formula works like a charm. It returns my entire table and changes date format in column B:
As you may have noticed, to change the date format via the formula, I used special codes that represent different looks of days, months, and years. If you're not familiar with them, here's a list of these codes for dates:
|d||Day without a leading zero for 1-9||7|
|dd||Day with a leading zero for 1-9||07|
|ddd||Day as an abbreviation||Wed|
|dddd||Day as a full name||Wednesday|
(if not preceded or followed by
hours or seconds)
|Month without a leading zero||8|
(if not preceded or followed by
hours or seconds)
|Month with a leading zero||08|
|mmm||Month as an abbreviation||Aug|
|mmmm||Month as a full name||August|
|mmmmm||First letter of the month||A|
|Two digit year||19|
|Full numeric year||2019|
Tip. If you'd like to supply your date format with time as well, you need to add codes for time units. You will find the full list of time codes in this guide.
Using these codes, you can format the dates in so many ways:
=QUERY(A1:C7,"select * format B 'yyyy'")
=QUERY(A1:C7,"select * format B 'dd mmmm, dddd'")
By the way, what date format have you got used to? :)
In case you need to see numbers instead of dates, one of the methods below will be useful.
Another way for Google Sheets to convert date to number is by using the DATEVALUE function:
where date_string represents any date in the known for spreadsheets format. The date should be put in double-quotes.
For example, I want to convert August 17, 2019 to a number. All the formulas below will return the same result: 43694.
=DATEVALUE("August 17, 2019")
Tip. If you're not sure whether Google Sheets understands the format you're about to enter, try typing the date into another cell first. If the date is recognized, it'll be aligned to the right.
You can also fill your cells with dates in one column, and then reference them in your formulas in another column:
Converting dates to text in spreadsheets is the task for the TEXT function:
Tip. To set the format correctly, use the same codes as you did for the QUERY function.
The real-data formula may look like this:
Here's how I converted my date – 8/17/2019 - to text and changed the format at the same time:
This is it! I hope by now you know how to change date format in Google Sheets and convert dates to numbers or text. Feel free to share other cool ways in the comments section below. ;)
Table of contents