How To Stop Google Sheets From Changing Dates

Dates are an inevitable function of Google Sheets. And like many other concepts of spreadsheets, they require a flake of learning.

In this tutorial, you will find out how Google stores dates and how you can format them for your amend convenience. Some date formats are offered to you by spreadsheets while others should be created from scratch. At that place are fifty-fifty a couple of handy functions for the task.

I also describe a couple of means of how to convert your dates to numbers and text if necessary.

• How Google Sheets formats dates
• Alter engagement format to some other locale
• How to modify date format in Google Sheets
  • Default Google Sheets engagement format
  • Custom date formats
  • QUERY office for Google Sheets to format dates
• Google Sheets: catechumen engagement to number
• Google Sheets: convert date to text

How Google Sheets formats dates

First things first: before whatever activities related to dates in spreadsheets, it'southward crucial to understand the core principles of how dates work.

For its internal database, Google Sheets stores all dates equally integer numbers. Non sequences of a twenty-four hours, month, and year as nosotros got used to seeing, just simple integers:

• ane for Dec 31, 1899
• 2 for January i, 1900
• 102 for April xi, 1900 (100 days after January 1, 1900)
• and so on.

Unlike Excel that cannot shop dates as negative numbers, in Google, for dates prior to December 31, 1899, the numbers will be negative:

• -i for December 29, 1899
• -ii for December 28, 1899
• -102 for September 19, 1899
• etc.

Regardless of how Google Sheets formats dates for you to come across in cells, spreadsheets always store them every bit integers. It'due south an automatic Google Sheets appointment format that helps to treat dates correctly.

Tip. The same goes for time units – they are but decimals for your tabular array:

• .00 for 12:00 AM
• .l for 12:00 PM
• .125 for 3:00 AM
• .573 for 1:45 PM
• etc.

A date paired with fourth dimension is kept as an integer with decimal places:

• 31,528.058 is April 26, 1986, i:23 AM
• 43,679.813 is August 2, 2019, 7:thirty PM

Alter date format in Google Sheets to another locale

Some other of import 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 yous're currently in the US, 06-Aug-2019 will be put every bit 8/half dozen/2019 in your sheet, while for the UK it'll be 6/eight/2019.

To ensure the correct calculations, it's vital to take the correct locale set, specially if the file was created in another country:

1. Go to File > Spreadsheet settings in the Google Sheets menu.
2. Find Locale under the General tab and pick the desired location from the drop-down listing:
   

Tip. Every bit a bonus, you can likewise specify your fourth dimension zone here to record your file history in it.

Note. The locale doesn't change the linguistic communication of your Sheets. However, the date formatting will exist practical to the entire spreadsheet. Everyone who works with it volition see the changes, no matter their place on the globe.

How to alter date format in Google Sheets

If dates in your tables are formatted inconsistently or all yous tin come across is strange sets of numbers instead, don't panic. You simply need to alter the appointment format in your Google Sheets using built-in instruments.

Default Google Sheets date format

1. Select all cells you'd like to format.
2. Go to Format > Number in the spreadsheet carte and pick Appointment to run into the date only or Date time to get both date and time in a jail cell:
   

The integers successfully plough into the format that you'll recognize at a glance. These are default Google Sheets date formats:

Tip. Yous can find the same formats if you lot click on the 123 icon on the spreadsheet toolbar:

Custom appointment 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 tin access them from the aforementioned Google Sheets card: Format > Number > More formats > More date and time formats:

You will see the window with lots of different custom engagement formats available. Whichever 1 yous chose and apply, your dates volition look the same:

If you're withal not happy with the appearance of your dates, you tin can tailor your own custom date format:

1. Select cells you desire to format.
2. Go to Format > Number > More formats > More than date and time formats.
3. Place the cursor into the field at the top that contains the date units and delete everything with your Backspace or Delete keys:
   
4. Click the pointer to the right of the field and pick the unit yous'd like to have first. Don't forget to type the separator afterwards.

   Repeat till all necessary units are added (no worries, you lot'll be able to add or remove them later on):
   

5. Notice that each unit has double arrows to its correct. Click them and you'll be able to arrange the exact style to brandish the value.

   Here'south what I can choose for Day:
   

   This way, you tin can edit all values, insert additional and delete obsolete ones. You are free to separate the units with diverse characters including commas, slashes, and dashes.

6. Once y'all're fix, click Apply.

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

QUERY office for Google Sheets to format dates

There's 1 more way to change engagement format in Google Sheets – with a formula, of course. Since this is non the first time of me showing QUERY to yous, I'yard starting to think of it as a real catholicon for spreadsheets. :)

I have an example table where I track the shipment of a few orders:

I want to change the appointment format in column B. Here's my QUERY formula:

=QUERY(A1:C7,"select * format B 'd-mmm-yy (ddd)'")

• first, I specify the range of my entire table – A1:C7
• then I'm asking the formula to return all columns – select *
• and at the same time re-format cavalcade B the style I put into the formula – format B 'd-mmm-yy (ddd)'

The formula works like a charm. It returns my entire table and changes date format in cavalcade B:

As yous may have noticed, to alter the date format via the formula, I used special codes that represent different looks of days, months, and years. If you're non familiar with them, here'southward a list of these codes for dates:

Code	Description	Case
d	Day without a leading zero for i-9	seven
dd	Day with a leading zero for 1-nine	07
ddd	Day every bit an abbreviation	Wed
dddd	Day as a full proper name	Wednesday
one thousand (if non preceded or followed past hours or seconds)	Month without a leading goose egg	8
mm (if not preceded or followed past hours or seconds)	Calendar month with a leading aught	08
mmm	Month as an abbreviation	Aug
mmmm	Month as a full name	August
mmmmm	Showtime letter of the month	A
y or yy	Two digit year	19
yyy or yyyy	Full numeric yr	2019

Tip. If you'd similar to supply your date format with time as well, y'all demand to add together codes for time units. You volition discover the full list of time codes in this guide.

Using these codes, you tin format the dates in so many means:

• Get the concur of the year, calendar month, or day only:

  =QUERY(A1:C7,"select * format B 'yyyy'")

• Return the solar day, month, and the mean solar day of the week:

  =QUERY(A1:C7,"select * format B 'dd mmmm, dddd'")

Past the way, what date format have you got used to? :)

Google Sheets: convert date to number

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

Convert engagement to number past changing the format

1. Select those cells with dates that you lot desire to convert to numbers.
2. Go to Format > Number and this fourth dimension pick Number among other options.
3. Voila – all selected dates have turned into numbers that stand for them:
   

DATEVALUE function for Google Sheets

Another fashion for Google Sheets to convert date to number is by using the DATEVALUE office:

=DATEVALUE(date_string)

where date_string represents any date in the known for spreadsheets format. The date should be put in double-quotes.

For example, I desire to convert August 17, 2019 to a number. All the formulas below volition render the same result: 43694.

=DATEVALUE("August 17, 2019")
=DATEVALUE("2019-viii-17")
=DATEVALUE("8/17/2019")

Tip. If you're not sure whether Google Sheets understands the format you're nearly to enter, endeavour typing the date into another jail cell start. If the engagement is recognized, information technology'll exist aligned to the right.

You can also fill your cells with dates in one column, so reference them in your formulas in another column:

=DATEVALUE(A2)

Google Sheets: convert engagement to text

Converting dates to text in spreadsheets is the task for the TEXT role:

=TEXT(number,format)

• number – regardless of what number, date, or time you requite to the function, it volition render it equally text.
• format – the text will be formatted the manner you specify in the formula.

  Tip. To set the format correctly, use the aforementioned codes as y'all did for the QUERY part.

The existent-data formula may await like this:

=TEXT("viii/17/2019","YYYY-MM-DD")

Here's how I converted my appointment – viii/17/2019 - to text and inverse the format at the same time:

This is it! I promise by at present 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. ;)

You may also be interested in

Source: https://www.ablebits.com/office-addins-blog/google-sheets-change-date-format/

Share this post