How to convert date to date format

Date and Time Conversions Using SQL Server

Did you know that MSSQLTips.com publishes new SQL Server content on a daily basis as well as offers free webinars and tutorials?

We know your day is hectic and you don’t necessarily have time to research new topics and solutions every day, but we can keep you informed.

Take 30 seconds to register for our newsletter and look for free educational content to help you grow your career. >> REGISTER HERE

Problem

There are many instances when dates and times don’t show up at your doorstep in the format you’d like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.

Solution

SQL Server provides a number of options you can use for formatting a date/time string in SQL queries and stored procedures either from an input file (Excel, CSV, etc.) or a date column (datetime, datetime2, smalldatetime, etc.) from a table. One of the first considerations is the actual date/time value needed. The most common is the current date/time using getdate(). This provides the current date and time according to the server providing the date and time. If a universal date/time (UTC) is needed, then getutcdate() should be used. To change the format of the date, you convert the requested date to a string and specify the format number corresponding to the format needed.

How to get different date formats in SQL Server

Below is a list of SQL date formats and an example of the output. The date used for all of these examples is «2006-12-30 00:38:54.840».

DATE ONLY FORMATS
Format #	Query	Format	Sample
1	select convert(varchar, getdate(), 1)	mm/dd/yy	12/30/06
2	select convert(varchar, getdate(), 2)	yy.mm.dd	06.12.30
3	select convert(varchar, getdate(), 3)	dd/mm/yy	30/12/06
4	select convert(varchar, getdate(), 4)	dd.mm.yy	30.12.06
5	select convert(varchar, getdate(), 5)	dd-mm-yy	30-12-06
6	select convert(varchar, getdate(), 6)	dd-Mon-yy	30 Dec 06
7	select convert(varchar, getdate(), 7)	Mon dd, yy	Dec 30, 06
10	select convert(varchar, getdate(), 10)	mm-dd-yy	12-30-06
11	select convert(varchar, getdate(), 11)	yy/mm/dd	06/12/30
12	select convert(varchar, getdate(), 12)	yymmdd	061230
23	select convert(varchar, getdate(), 23)	yyyy-mm-dd	2006-12-30
101	select convert(varchar, getdate(), 101)	mm/dd/yyyy	12/30/2006
102	select convert(varchar, getdate(), 102)	yyyy.mm.dd	2006.12.30
103	select convert(varchar, getdate(), 103)	dd/mm/yyyy	30/12/2006
104	select convert(varchar, getdate(), 104)	dd.mm.yyyy	30.12.2006
105	select convert(varchar, getdate(), 105)	dd-mm-yyyy	30-12-2006
106	select convert(varchar, getdate(), 106)	dd Mon yyyy	30 Dec 2006
107	select convert(varchar, getdate(), 107)	Mon dd, yyyy	Dec 30, 2006
110	select convert(varchar, getdate(), 110)	mm-dd-yyyy	12-30-2006
111	select convert(varchar, getdate(), 111)	yyyy/mm/dd	2006/12/30
112	select convert(varchar, getdate(), 112)	yyyymmdd	20061230
TIME ONLY FORMATS
8	select convert(varchar, getdate(), 8)	hh:mm:ss	00:38:54
14	select convert(varchar, getdate(), 14)	hh:mm:ss:nnn	00:38:54:840
24	select convert(varchar, getdate(), 24)	hh:mm:ss	00:38:54
108	select convert(varchar, getdate(), 108)	hh:mm:ss	00:38:54
114	select convert(varchar, getdate(), 114)	hh:mm:ss:nnn	00:38:54:840
DATE & TIME FORMATS
0	select convert(varchar, getdate(), 0)	Mon dd yyyy hh:mm AM/PM	Dec 30 2006 12:38AM
9	select convert(varchar, getdate(), 9)	Mon dd yyyy hh:mm:ss:nnn AM/PM	Dec 30 2006 12:38:54:840AM
13	select convert(varchar, getdate(), 13)	dd Mon yyyy hh:mm:ss:nnn AM/PM	30 Dec 2006 00:38:54:840AM
20	select convert(varchar, getdate(), 20)	yyyy-mm-dd hh:mm:ss	2006-12-30 00:38:54
21	select convert(varchar, getdate(), 21)	yyyy-mm-dd hh:mm:ss:nnn	2006-12-30 00:38:54.840
22	select convert(varchar, getdate(), 22)	mm/dd/yy hh:mm:ss AM/PM	12/30/06 12:38:54 AM
25	select convert(varchar, getdate(), 25)	yyyy-mm-dd hh:mm:ss:nnn	2006-12-30 00:38:54.840
100	select convert(varchar, getdate(), 100)	Mon dd yyyy hh:mm AM/PM	Dec 30 2006 12:38AM
109	select convert(varchar, getdate(), 109)	Mon dd yyyy hh:mm:ss:nnn AM/PM	Dec 30 2006 12:38:54:840AM
113	select convert(varchar, getdate(), 113)	dd Mon yyyy hh:mm:ss:nnn	30 Dec 2006 00:38:54:840
120	select convert(varchar, getdate(), 120)	yyyy-mm-dd hh:mm:ss	2006-12-30 00:38:54
121	select convert(varchar, getdate(), 121)	yyyy-mm-dd hh:mm:ss:nnn	2006-12-30 00:38:54.840
126	select convert(varchar, getdate(), 126)	yyyy-mm-dd T hh:mm:ss:nnn	2006-12-30T00:38:54.840
127	select convert(varchar, getdate(), 127)	yyyy-mm-dd T hh:mm:ss:nnn	2006-12-30T00:38:54.840
ISLAMIC CALENDAR DATES
130	select convert(nvarchar, getdate(), 130)	dd mmm yyyy hh:mi:ss:nnn AM/PM
131	select convert(nvarchar, getdate(), 131)	dd mmm yyyy hh:mi:ss:nnn AM/PM	10/12/1427 12:38:54:840AM

You can also format the date or time without dividing characters, as well as concatenate the date and time string:

If you want to get a list of all valid date and time formats, you could use the code below and change the @date to GETDATE() or any other date you want to use. This will output just the valid formats.

Recommended Reading

Continue your learning on Microsoft SQL Server dates with these tips and tutorials:

Next Steps

SQLShack

SQL convert date

Introduction

A common task for newbies is to learn how to do a SQL convert date and work to convert them date to other data types or covert other data types to Date.

Here in this article we will explain how to work and convert dates to different formats or vice versa.

Requirements

Example

The first example will be simple, we have a varchar column with a date in a table, but we need to convert the varchar to date. We need to do a SQL convert date.

Here it is script to create the table with data:

We want to convert the column date from nvarchar(100) to a date.

To do it, we are going to try to modify the design of the table:

We will try to change the Data Type to smalldatetime:

You will receive the following error message:

Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

To solve this error, in SSMS go to Tools > Options menu:

In Options, go to Designers >Table and Database Designers and uncheck the Prevent saving changes that require table re-creation:

This option will disable to option to prevent saving table recreation. When you modify the column data type, it requires table re-creation.

Now, you can save the design and your table will be converted to date and the SQL convert date is completed:

Conversion functions

T-SQL contains functions to convert data types. We will use CAST and CONVERT to do a SQL convert date.

Let’s start with CAST first:

How to convert from varchar, nvarchar, char, nchar to sql date using CAST

The following example, will show how to convert characters to a datetime date type using the CAST function:

How to change date format in Excel and create custom formatting

by Svetlana Cheusheva | updated on November 2, 2021

The first part of our tutorial focuses of formatting dates in Excel and explains how to set the default date and time formats, how to change date format in Excel, how to create custom date formatting, and convert your dates to another locale.

Along with numbers, dates and times are the most common data types people use in Excel. However, they may be quite confusing to work with, firstly, because the same date can be displayed in Excel in a variety of ways, and secondly, because Excel always internally stores dates in the same format regardless of how you have formatted a date in a given cell.

Knowing the Excel date formats a little in depth can help you save a ton of your time. And this is exactly the aim of our comprehensive tutorial to working with dates in Excel. In the first part, we will be focusing on the following features:

Excel date format

Before you can take advantage of powerful Excel date features, you have to understand how Microsoft Excel stores dates and times, because this is the main source of confusion. While you would expect Excel to remember the day, month and the year for a date, that’s not how it works.

Excel stores dates as sequential numbers and it is only a cell’s formatting that causes a number to be displayed as a date, time, or date and time.

Dates in Excel

All dates are stored as integers representing the number of days since January 1, 1900, which is stored as number 1, to December 31, 9999 stored as 2958465.

Time in Excel

Dates & Times in Excel

Excel stores dates and times as decimal numbers comprised of an integer representing the date and a decimal portion representing the time.

How to convert date to number in Excel

If you want to know what serial number represents a certain date or time displayed in a cell, you can do this in two ways.

1. Format Cells dialog

Select the cell with a date in Excel, press Ctrl+1 to open the Format Cells window and switch to the General tab.

If you just want to know the serial number behind the date, without actually converting date to number, write down the number you see under Sample and click Cancel to close the window. If you want to replace the date with the number in a cell, click OK.

2. Excel DATEVALUE and TIMEVALUE functions

To know both, date and time, concatenate these two functions in the following way:

=DATEVALUE(«1/1/2015») & TIMEVALUE(«6:00 AM»)

Note. Since Excel’s serial numbers begins on January 1, 1900 and negative numbers aren’t recognized, dates prior to the year 1900 are not supported in Excel.

If you are dealing with date and time values and you’d like to convert time to decimal number, please check out the formulas described in this tutorial: How to convert time to decimal number in Excel.

Default date format in Excel

When you work with dates in Excel, the short and long date formats are retrieved from your Windows Regional settings. These default formats are marked with an asterisk (*) in the Format Cell dialog window:

The default date and time formats in the Format Cell box change as soon as you change the date and time settings in Control Panel, which leads us right to the next section.

How to change the default date and time formats in Excel

If you want to set a different default date and/or time formats on your computer, for example change the USA date format to the UK style, go to Control panel and click Region and Language. If in your Control panel opens in Category view, then click Clock, Language, and Region > Region and Language > Change the date, time, or number format.

On the Formats tab, choose the region under Format, and then set the date and time formatting by clicking on an arrow next to the format you want to change and selecting the desired one from the drop-down list:

Tip. If you are not sure what different codes (such as mmm, ddd, yyy) mean, click the «What does the notation mean» link under the Date and time formats section, or check the Custom Excel date formats in this tutorial.

If you are not happy with any time and date format available on the Formats tab, click the Additional settings button in the lower right-hand side of the Region and Language dialog window. This will open the Customize dialog, where you switch to the Date tab and enter a custom short or/and long date format in the corresponding box.

How to quickly apply default date and time formatting in Excel

To quickly change date format in Excel to the default formatting, do the following:

If you want more date formatting options, either select More Number Formats from the drop-down list or click the Dialog Box Launcher next to Number. This will open a familiar Format Cells dialog and you can change date format there.

How to change date format in Excel

In Microsoft Excel, dates can be displayed in a variety of ways. When it comes to changing date format of a given cell or range of cells, the easiest way is to open the Format Cells dialog and choose one of the predefined formats.

If the date format is not changing in your Excel sheet, most likely your dates are formatted as text and you have to convert them to the date format first.

How to convert date format to another locale

Once you’ve got a file full of foreign dates and you would most likely want to change them to the date format used in your part of the world. Let’s say, you want to convert an American date format (month/day/year) to a European style format (day/month/year).

The easiest way to change date format in Excel based on how another language displays dates is as follows:

If you want the dates to be displayed in another language, then you will have to create a custom date format with a locale code.

Creating a custom date format in Excel

If none of the predefined Excel date formats is suitable for you, you are free to create your own.

Tip. The easiest way to set a custom date format in Excel is to start from an existing format close to what you want. To do this, click Date in the Category list first, and select one of existing formats under Type. After that click Custom and make changes to the format displayed in the Type box.

When setting up a custom date format in Excel, you can use the following codes.

When setting up a custom time format in Excel, you can use the following codes.

To set up date and time format, include both date and time units in your format code, e.g. m/d/yyyy h:mm AM/PM. When you use «m» immediately after «hh» or «h» or immediately before «ss» or «s», Excel will display minutes, not a month.

When creating a custom date format in Excel, you can use a comma (,) dash (-), slash (/), colon (:) and other characters.

For example, the same date and time, say January 13, 2015 13:03, can be displayed in a various ways:

How to create a custom Excel date format for another locale

If you want to display dates in another language, you have to create a custom format and prefix a date with a corresponding locale code. The locale code should be enclosed in [square brackets] and preceded with the dollar sign ($) and a dash (-). Here are a few examples:

You can find the full list of locale codes on this blog.

For example, this is how you set up a custom Excel date format for the Chinese locale in the year-month-day (day of the week) time format:

The following image shows a few examples of the same date formatted with different locale codes in the way traditional for the corresponding languages:

Usually, Microsoft Excel understands dates very well and you are unlikely to hit any roadblock when working with them. If you happen to have an Excel date format problem, please check out the following troubleshooting tips.

A cell is not wide enough to fit an entire date

If you see a number of pound signs (#####) instead of dates in your Excel worksheet, most likely your cells are not wide enough to fit the whole dates.

Solution. Double-click the right border of the column to resize it to auto fit the dates. Alternatively, you can drag the right border to set the column width you want.

Negative numbers are formatted as dates

In all modern versions of Excel 2013, 2010 and 2007, hash marks (#####) are also displayed when a cell formatted as a date or time contains a negative value. Usually it’s a result returned by some formula, but it may also happen when you type a negative value into a cell and then format that cell as a date.

If you want to display negative numbers as negative dates, two options are available to you:

Solution 1. Switch to the 1904 date system.

Go to File > Options > Advanced, scroll down to the When calculating this workbook section, select the Use 1904 date system check box, and click OK.

Of course, such representation is very unusual and takes time to get used to, but this is the right way to go if you want to perform calculations with early dates.

Solution 2. Use the Excel TEXT function.

Another possible way to display negative numbers as negative dates in Excel is using the TEXT function. For example, if you are subtracting C1 from B1 and a value in C1 is greater than in B1, you can use the following formula to output the result in the date format:

=TEXT(ABS(B1-C1),»-d-mmm-yyyy»)

You may want to change the cell alignment to right justified, and naturally, you can use any other custom date formats in the TEXT formula.

Note. Unlike the previous solution, the TEXT function returns a text value, that is why you won’t be able to use the result in other calculations.

Dates are imported to Excel as text values

Solution. You can convert «text dates» to the date format using Excel’s DATEVALUE function or Text to Columns feature. Please see the following article for full details: How to convert text to date in Excel.

This is how you format dates in Excel. In the next part of our guide, we will discuss various ways of how you can insert dates and times in your Excel worksheets. Thank you for reading and see you next week!

How to convert text to date and number to date in Excel

by Svetlana Cheusheva | updated on July 18, 2022

The tutorial explains how to use Excel functions to convert text to date and number to date, and how to turn text strings into dates in a non-formula way. You will also learn how to quickly change a number to date format.

There are many ways to convert text to date in Excel and this tutorial aims to cover them all, so that you can choose a text-to-date conversion technique most suitable for your data format and your preference for a formula or non-formula way.

How to distinguish normal Excel dates from «text dates»

When importing data into Excel, there is often a problem with date formatting. The imported entries may look like normal Excel dates to you, but they don’t behave like dates. Microsoft Excel treats such entries as text, meaning you cannot sort your table by date properly, nor can you use those «text dates» in formulas, PivotTables, charts or any other Excel tool that recognizes dates.

There are a few signs that can help you determine whether a given entry is a date or a text value.

How to convert number to date in Excel

Since all Excel functions that change text to date return a number as a result, let’s have a closer look at converting numbers to dates first.

As you probably know, Excel stores dates and times as serial numbers and it is only a cell’s formatting that forces a number to be displayed as a date. For example, 1-Jan-1900 is stored as number 1, 2-Jan-1900 is stored as 2, and 1-Jan-2015 is stored as 42005. For more information on how Excel stores dates and times, please see Excel date format.

When calculating dates in Excel, the result returned by different date functions is often a serial number representing a date. For example, if =TODAY()+7 returns a number like 44286 instead of the date that is 7 days after today, that does not mean the formula is wrong. Simply, the cell format is set to General or Text while it should be Date.

To convert such serial number to date, all you have to do is change the cell number format. For this, simply pick Date in the Number Format box on the Home tab.

To apply a format other than default, then select the cells with serial numbers and press Ctrl+1 to open the Format Cells dialog. On the Number tab, choose Date, select the desired date format under Type and click OK.

Yep, it’s that easy! If you want something more sophisticated than predefined Excel date formats, please see how to create a custom date format in Excel.

How to convert 8-digit number to date in Excel

To convert such a number to date, you will have to use the DATE function in combination with RIGHT, LEFT and MID functions. Unfortunately, it is not possible to make a universal formula that will work in all scenarios because the original number can be input in a variety of different formats. For example:

Anyway, I will try to explain the general approach to converting such numbers to dates and provide a few formula examples.

For starters, remember the order of the Excel Date function arguments:

So, what you need to do is extract a year, month and date from the original number and supply them as the corresponding arguments to the Date function.

For example, let’s see how you can convert number 10032016 (stored in cell A1) to date 3/10/2016.

Finally, embed the above ingredients into the Date function, and you get a formula to convert number to date in Excel:

=DATE(RIGHT(A1,4), MID(A1,3,2), LEFT(A1,2))

The following screenshot demonstrates this and a couple more formulas in action:

Please pay attention to the last formula in the above screenshot (row 6). The original number-date (161003) contains only 2 chars representing a year (16). So, to get the year of 2016, we concatenate 20 and 16 using the following formula: 20&LEFT(A6,2). If you don’t do this, the Date function will return 1916 by default, which is a bit weird as if Microsoft still lived in the 20 th century 🙂

Note. The formulas demonstrated in this example work correctly as long as all numbers you want to convert to dates follow the same pattern.

How to convert text to date in Excel

When you spot text dates in your Excel file, most likely you would want to convert those text strings to normal Excel dates so that you can refer to them in your formulas to perform various calculations. And as is often the case in Excel, there are a few ways to tackle the task.

The DATEVALUE function in Excel converts a date in the text format to a serial number that Excel recognizes as a date.

The syntax of Excel’s DATEVALUE is very straightforward:

Because the Excel DATEVALUE function converts a text date to a serial number, you will have to make that number look like a date by applying the Date format to it, as we discussed a moment ago.

The following screenshots demonstrates a few Excel DATEVALUE formulas in action:

When converting a text string to a date using the DATEVALUE function, please keep in mind that:

Compared to DATEVALUE, the Excel VALUE function is more versatile. It can convert any text string that looks like a date or number into a number, which you can easily change to a date format of your choosing.

The syntax of the VALUE function is as follows:

Where text is a text string or reference to a cell containing the text you want to convert to number.

The Excel VALUE function can process both date and time, the latter is converted to a decimal portion, as you can see in row 6 in the following screenshot:

Mathematical operations to convert text to dates

Apart from using specific Excel functions such as VALUE and DATEVALUE, you can perform a simple mathematical operation to force Excel to do a text-to-date conversion for you. The required condition is that an operation should not change the date’s value (serial number). Sounds a bit tricky? The following examples will make things easy!

Assuming that your text date is in cell A1, you can use any of the following formulas, and then apply the Date format to the cell:

As you can see in the above screenshot, mathematical operations can convert dates (rows 2 and 4), times (row 6) as well as numbers formatted as text (row 8). Sometimes the result is even displayed as a date automatically, and you don’t have to bother about changing the cell format.

How to convert text strings with custom delimiters to dates

If your text dates contain some delimiter other than a forward slash (/) or dash (-), Excel functions won’t be able to recognize them as dates and return the #VALUE! error.

To fix this, you can run Excel’s Find and Replace tool to replace your delimiter with a slash (/), all in one go:

Now, the DATEVALUE or VALUE function should have no problem with converting the text strings to dates. In the same manner, you can fix dates containing any other delimiter, e.g. a space or a backward slash.

If you prefer a formula solution, you can use Excel’s SUBSTITUTE function instead of Replace All to switch your delimiters to slashes.

Assuming the text strings are in column A, a SUBSTITUTE formula may look as follows:

Where A1 is a text date and «.» is the delimiter your strings are separated with.

Now, let’s embed this SUBSTITUTE function into the VALUE formula:

And have the text strings converted to dates, all with a single formula.

As you see, the Excel DATEVALUE and VALUE functions are quite powerful, but both have their limits. For example, if you are trying to convert complex text strings like Thursday, January 01, 2015, neither function could help. Luckily, there is a non-formula solution that can handle this task and the next section explains the detailed steps.

If you are a non-formula user type, a long-standing Excel feature called Text To Columns will come in handy. It can cope with simple text dates demonstrated in Example 1 as well as multi-part text strings shown in Example 2.

Example 1. Converting simple text strings to dates

If the text strings you want to convert to dates look like any of the following:

You don’t really need formulas, nor exporting or importing anything. All it takes is 5 quick steps.

In this example, we will be converting text strings like 01 01 2015 (day, month and year are separated with spaces) to dates.

In this example, we are converting the text dates formatted as «01 02 2015» (month day year), so we select MDY from the drop down box.

Now, Excel recognizes your text strings as dates, automatically converts them to your default date format and displays right-aligned in the cells. You can change the date format in the usual way via the Format Cells dialog.

Note. For the Text to Column wizard to work correctly, all of your text strings should be formatted identically. For example, if some of your entries are formatted like day/month/year format while others are month/day/year, you would get incorrect results.

Example 2. Converting complex text strings to dates

If your dates are represented by multi-part text strings, such as:

You will have to put a bit more effort and use both the Text to Columns wizard and Excel DATE function.

It also makes sense to select the «Treat consecutive delimiters as one» option to ignore extra spaces, if your data has any.

Note. Do not choose the Date format for any column because each column contains only one component, so Excel won’t be able to understand this is a date.

If you don’t need some column, click on it and select Do not import column (skip).

When done, click the Finish button.

As you see in the screenshot above, we are skipping the first column with the days of the week, splitting the other data into 3 columns (in the General format) and inserting these columns beginning from cell C2.

The following screenshot shows the result, with the original data in column A and the split data in columns C, D and E.

In our case, year is in column E and day is in column D, no problem with these.

It’s not so easy with month because it is text while the DATE function needs a number. Luckily, Microsoft Excel provides a special MONTH function that can change a month’s name to a month’s number:

For the MONTH function to understand it deals with a date, we put it like this:

Where C2 contains the name of the month, January in our case. «1&» is added to concatenate a date (1 January) so that the MONTH function can convert it to the corresponding month number.

And now, let’s embed the MONTH function into the month ; argument of our DATE formula:

And voila, our complex text strings are successfully converted to dates:

Quick conversion of text dates using Paste Special

To quickly convert a range of simple text strings to dates, you can use the following trick.

To learn more about the Paste Special feature, please see How to use Paste Special in Excel.

Fixing text dates with two-digit years

The modern versions of Microsoft Excel are smart enough to spot some obvious errors in your data, or better say, what Excel considers an error. When this happens, you will see an error indicator (a small green triangle) in the upper-left corner of the cell and when you select the cell, an exclamation mark appears:

Clicking the exclamation mark will display a few options relevant to your data. In case of a 2-digit year, Excel will ask if you want to convert it to 19XX or 20XX.

How to turn on Error Checking in Excel

Usually, Error Checking is enabled in Excel by default. To make sure, click File > Options > Formulas, scroll down to the Error Checking section and verify if the following options are checked:

How to change text to date in Excel an easy way

As you see, converting text to date in Excel is far from being a trivial one-click operation. If you are confused by all different use cases and formulas, let me show you a quick and straightforward way.

Install our Ultimate Suite (a free trial version can be downloaded here), switch to the Ablebits Tools tab (2 new tabs containing 60+ awesome tools will be added to your Excel!) and find the Text to Date button:

To convert text-dates to normal dates, here’s what you do:

That’s it! The results of conversion will appear in the adjacent column, your source data will be preserved. If something goes wrong, you can simply delete the results and try again with a different date order.

Tip. If you chose to convert times as well as dates, but the time units are missing in the results, be sure to apply a number format that shows both the date and time values. For more info, please see How to create custom date and time formats.

If you are curious to learn more about this wonderful tool, please check out its home page: Text to Date for Excel.

This is how you convert text to date in Excel and change dates to text. Hopefully, you have been able to find a technique to your liking. In the next article, we will tackle the opposite task and explore different ways of converting Excel dates to text strings. I thank you for reading and hope to see you next week.

How to change Google Sheets date format and convert dates to numbers and text

by Natalia Sharashova | updated on November 22, 2021

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.

How Google Sheets formats dates

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:

Change date format in Google Sheets to another locale

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.

How to change date format in Google Sheets

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.

Default Google Sheets date format

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:

Custom date formats

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:

Here’s what format I’ve created and how my dates look now:

QUERY function for Google Sheets to format dates

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:

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 ‘dd mmmm, dddd'»)

By the way, what date format have you got used to? 🙂

Google Sheets: convert date to number

In case you need to see numbers instead of dates, one of the methods below will be useful.

Convert date to number by changing the format

DATEVALUE function for Google Sheets

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»)
=DATEVALUE(«2019-8-17»)
=DATEVALUE(«8/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:

Google Sheets: convert date to text

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:

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. 😉