Formulas for dates in Google Sheets

The key to overcoming your struggle with date formats in Google Sheets is to understand the prevailing standardization and then apply it clearly and consistently. It requires a bit of learning to understand this concept of spreadsheets. In this article, you will find out about the various date formats in Google Sheets and how to use them for your better convenience.

What is the date format in Google Sheets?

The date format in Google Sheets is a standard way provided by Google Sheets to express a particular period of the day (D), month (M), and year (Y) in a numeric calendar date, which helps you eliminate ambiguity:

• Day can be written
  • In numbers (one or two digits) as the day of month, for example, 2 or 12
  • Abbreviated day of the week (three digits), for example, Tue
  • Full day name of the week, for example, Tuesday
• Month can be written
  • In numbers (one or two digits) as the month in year, for example, 1 or 12
  • Abbreviated month (three digits), for example, Apr
  • Full month name, for example, April
• Year can be written
  • in two or four digits, for example, 21 or 2021.

The separators you can use between the components are slash (/), dot (.), dash (-), and space ().

Google Sheets has many options to help meet your unique circumstances. When you do this, you have two choices:

• Use the default formats

Default formats here mean the default/specific date formats that are used in your region/country. Suppose you live in the UK, then the default date format that you can use is DD/MM/YYYY 24/04/1997, for example.

• Create a custom format

With this choice, you are free to define the date format, as long as the format meets the criteria in Google Sheets. For example, after the date format MM-DD-YYYY, you want to add hh:mm as a two-digit number for the hour and a two-digit number for the minute 04-15-2017 10:14 AM.

Date time formats in Google Sheets

The date formats in Google Sheets vary all over the world, with different separations among them. Some people manipulate date formats into their spreadsheets in textual values such as yesterday or Tuesday, while others input in advanced formats such as 26/3/2001 02:05 PM. There is also a cross-cultural audience that represents dates in different ways that could be incompatible with other audiences for example in the United States, the date format is MM/DD/YYYY, while most of Europe uses DD/MM/YYYY. This difference could confuse people.

Below is the table of some countries that have different date formats in Google Sheets. For example, April 3, 2000 will look as 03.04.2000 for people in Germany and 04.03.2000 for the United States. Without the information that is written in column C, you would get confused because the dates interpretation will be different between these countries.

To eliminate the ambiguity of the problem above, this article explains how to understand and apply various date formats in certain cases in Google Sheets. But first, please set your Google Sheets location. Find the details in the next section.

Google Sheets location what it is and why you need it

Google Sheets location is a setting in Google Sheets that controls how to enter number values (such as date-time values) into Google Sheets.This setting is what presets your Google Sheets date format based on your territory. For example, 18 August 2019 if youre in the US, the date format that will be put in your sheets is 08/18/2019, while in the UK, it will be 18/08/2019.

Its crucial to have the correct location set to ensure you use the correct date formats, especially if the file that you import into your Google Sheets was created in another country that has a different date format.

To set up your spreadsheet location, follow the steps below:

• In the Google Sheets menu, click File Spreadsheet settings.

• Under the General tab, you will find Locale pick the desired location from the drop-down list:

This Locale setting helps you ensure the correct date format using a regional standard date format. Now, you are ready to learn about using various date formats in Google Sheets.

How to format dates in Google Sheets

There are two ways to format dates in Google Sheets manually and using date functions. To explain each option, we imported some datasets from Excel files that are stored in OneDrive to Google Sheets. We did this automatically using Coupler.io, a solution for exporting data from different apps and sources to Google Sheets and BigQuery.

The available integrations include Pipedrive, Clockify, Jira and many more. Another benefit of this tool is that you can set up a schedule to refresh data automatically.

Format dates in Google Sheets manually

In the following dataset, lets change the format of data in column B into mm/dd/yyyy.

For this, select the range (B2:B11) and go to Format Number Date. Thats it

Tip: If you import data and you found that Google Sheets date format doesnt work with your data, find the solution for this case in this section.

Format dates in Google Sheets using functions

In Google Sheets, there are multiple date functions to format values in date or date-time format, and perform other date-centered manipulations. For example, the DATE function returns a date value of the specified year, month, and date values; the EDATE function returns a date based on the provided start date and the number of months before or after it. Here is a table with the date functions available in Google Sheets.

NameSyntaxDescriptionDATE=DATE(year, month, day)Converts a provided year, month, and day into a date. Learn moreDATEDIF=DATEDIF(start_date, end_date, unit)Calculates the number of days, months, or years between two dates. LearnmoreDATEVALUE=DATEVALUE(date_string)Converts a provided date string in a known format to a date value. LearnmoreDAY=DAY(date)Returns the day of the month that a specific date falls on, in numericformat. Learn moreDAYS=DAYS(end_date, start_date)Returns the number of days between two dates. Learn more.DAYS360=DAYS360(start_date, end_date, [method])Returns the difference between two days based on the 360 day year used insome financial interest calculations. Learn moreEDATE=EDATE(start_date, months)Returns a date a specified number of months before or after another date. LearnmoreEOMONTH=EOMONTH(start_date, months)Returns a date representing the last day of a month which falls aspecified number of months before or after another date. Learn moreHOUR=HOUR(time)Returns the hour component of a specific time, in numeric format. LearnmoreISOWEEKNUM=ISOWEEKNUM(date)Returns the number of the ISO week of the year where the provided datefalls. Learn moreMINUTE=MINUTE(time)Returns the minute component of a specific time, in numeric format. LearnmoreMONTH=MONTH(date)Returns the month of the year a specific date falls in, in numeric format. LearnmoreNETWORKDAYS=NETWORKDAYS(start_date, end_date, [holidays])Returns the number of net working days between two provided days. LearnmoreNETWORKDAYS.INTL=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])Returns the number of net working days between two provided days excludingspecified weekend days and holidays. Learn moreNOW=NOW()Returns the current date and time as a date value. Learn moreSECOND=SECOND(time)Returns the second component of a specific time, in numeric format. LearnmoreTIME=TIME(hour, minute, second)Converts a provided hour, minute, and second into a time. Learn moreTIMEVALUE=TIMEVALUE(time_string)Returns the fraction of a 24-hour day the time represents. Learn moreTODAY=TODAY()Returns the current date as a date value. Learn moreWEEKDAY=WEEKDAY(date, [type])Returns a number representing the day of the week of the date provided. LearnmoreWEEKNUM=WEEKNUM(date, [type])Returns a number representing the week of the year where the provided datefalls. Learn moreWORKDAY=WORKDAY(start_date, num_days, [holidays])Calculates the end date after a specified number of working days. LearnmoreWORKDAY.INTL=WORKDAY.INTL(start_date, num_days, [weekend], [holidays])Calculates the date after a specified number of workdays excludingspecified weekend days and holidays. Learn moreYEAR=YEAR(date)Returns the year specified by a given date. Learn moreYEARFRAC=YEARFRAC(start_date, end_date, [day_count_convention])Returns the number of years, including fractional years, between two datesusing a specified day count convention. Learn more

In this section, we will only focus on five date functions: TODAY, DAY, MONTH, YEAR, and DATE.

Google Sheets TODAY function

TODAY returns the current date on a cell keep in mind that the date inserted in this way will auto-update.

Syntax:

=TODAY()

Example:

Google Sheets DAY function

DAY returns the day from a given date.

Syntax:

=DAY(date)

Example:

Google Sheets MONTH Function

MONTH returns the month from a given date.

Syntax:

=MONTH(date)

Example:

Google Sheets YEAR Function

YEAR returns the year from a given date.

Syntax:

=YEAR(date)

Example:

Google Sheets DATE function

DATE converts a provided year, month, and day into a date. Because we use the US location, the date format is MM/DD/YYYY.

Syntax:

=DATE(year, month, day)

Example:

How to create a sheet with a specific date format in Google Sheets

The date format specific to your Google Sheets spreadsheet is affected by the specific regional date format you choose. For example:

• specific date format in the United States is MM/DD/YYYY
• specific date format in Europe is DD/MM/YYYY
• specific date format by ISO 8601 is YYYYMMDD or YYYY-MM-DD
• specific date format in Japan is YYYY/MM/DD
• and so on.

To choose one specific date format, you need to set up your Location in spreadsheets settings as we explained above. For example, the date format specific to the United States is MM/DD/YYYY. This means that if you enter the date December 24, 2020, it will look like 12/24/2020.

If you enter this date in another format, for example 24/12/2020, Google Sheets will recognize it as text rather than date.

How to set up custom date format in Google Sheets

Lets say you have an employee attendance that you just downloaded from your management app as shown below. You want to create a custom date format that will show day, month, and time only. You can customize the format by yourself with the following steps:

• Select the range C2:D13

• Go to Format Number More Formats More date and time formats

• When the custom date and time formats dialogue appears, find the date format by scrolling down the page. If you didnt find the date format you like, then customize it by adding, editing or removing the format components as shown below:

How to convert a date as month or year in Google Sheets?

If you need to convert the date into the month or year value only in Google Sheets, you can use MONTH, and YEAR functions.

Convert month value using the MONTH function

The MONTH function in Google Sheets is a function that returns the month number from a given date. The syntax is =MONTH(date).

For example, suppose we have a date 12/29/1993, which we need to convert into the month value. Use this syntax to extract the year

=MONTH(12/29/1993)

You can use different date formats as the parameter for the MONTH function. For example, 29/12/1993 will work.

But if you reference another cell in your MONTH formula, then make sure that this cell contains a date value. Otherwise, the formula will return an error.

Convert year value using the YEAR function

The logic of the YEAR function is the same it extracts the year value from a given date. The syntax is =YEAR(date). But, the date specified can be:

• a cell reference to a cell containing a date
• a function returning a date type
• a number

So, if you use just 12/29/1993 in your YEAR formula, it will return a wrong value.

But if you use a proper parameter in your formula, it will work:

Just like with the MONTH function, make sure that the cell you reference to in your YEAR formula contains a date value. Otherwise, the formula will return an error.

How to validate a date format in Google Sheets with pop-up alert

Suppose you have an overtime attendance as shown below, that can be edited by two people, you and your admin.

A common mistake that happens is your admin writes data in incorrect format or just simply paste something other than a date in the Timestamp column. To avoid this situation, you need to do Data validation to control what your admin can input in the Timestamp column. But first, assign the desired format as follows:

• Select the entire column C.
• Select Format > Number > More Formats > More Date and Time Formats.
• Select the format that you want for your column (for example, 8/5/1930)
• Click Apply.

Now you can apply Data Validation:

• Select column C (column that needs to date validation).
• Select Data Data Validation.
• Select Criteria: Date is a valid date.
• Select Reject input.
• Click the Appearance checkbox.
• Enter help text, for example, Enter a valid date in the format mm-dd-yyyy.
• Click Save.

Lets test the date validation that weve just created. Enter any text/number that is not in date format in any cell in column C. A warning message will pop up to remind us to input data correctly.

How to change the default date format in Google Sheets

Suppose you are a high school math teacher who downloaded your student data from an eLearning platform (probably using the JSON Client importer by Coupler.io :)).

You need to change the date format into the default date format of Google Sheets (MM/DD/YYYY). Here are the steps you should follow:

• Select the range (D2:E14).
• Click Format Number Date
• The selected cells changed the date to default.

Tip: You can click the 123 button in the top menu bar Dateas a shortcut.

How to set a date in Google Sheets based on a conditional format

If you want to highlight cells that meet certain criteria, conditional formatting is the best answer. This helps you better understand spreadsheets at a glance and create spreadsheets that are more human-readable by your whole team.

Note: For the examples in this article wherever todays date is written please keep in mind that todays date is 4/24/2021.

How to highlight dates in a range, row, or column if the date is todays date

How to highlight dates in a range

If you want to highlight todays date in a range, follow these steps:

• Select the range (A1:J5).

• Go to Format Conditional Formatting

• Customize your conditional format rules as follows:
  • Under Format cells if select Date is then select today in the next box (please see 1 in the picture below).
  • Click the fill color and choose red (please see 2 in the picture below).
  • Click Done.

Here is the result:

How to highlight dates in a row

Basically, if you pay attention to following the above steps, then for this case you can do it yourself. All you need to do is just adjust which row you want to highlight at the first step for example, select A1:1. Then repeat the flow above.

Highlight dates in a column

Here also, all you have to do is adjust which column you want to highlight at the first step, such as select the entire column A, then repeat the flow above.

Highlight dates in a cell

All you have to do is adjust which cell you want to highlight for the first step such as select cell A1, then repeat the flow above.

How to highlight an entire row if the date is todays date

Lets say, you want to highlight in blue anyone who registered at your gym today in an entire row. Complete the following steps:

• Select the range (A2:C7).

• Go to Format Conditional Formatting
• Customize your conditional format rules as follows:
  • Under Format cells if select custom formula is (please see 1 in the picture below).
  • Copy this formula =$A2=today() then paste in the next box (please see 2 in the picture below).
  • Click the fill color and choose blue (please see 3 in the picture below).
  • Click Done.

Here is the result:

How to highlight an entire column if the date is todays date

Can we highlight an entire column if the date is todays date? Yes, we can. Using the previous example, but in a different way to show the data, please follow the steps below:

• Select the range B1:G5.

• Go to Format Conditional Formatting
• Customize your conditional format rules as follows:
  • Under Format cells if select custom formula is (please see 1 on the picture below).
  • Copy this formula =B$1=today() then paste it in the next box (please see 2 on the picture below).
  • Click the fill colour and choose blue (please see 3 on the picture below).
  • Click Done.

Here is the result:

How to highlight weekends in Google Sheets using conditional format

We will customize the formula to highlight weekends in a range, row, cell or column in Google Sheets. For example:

• Suppose we highlight in red all weekends from April 1, 2021 to April 25, 2021. Select the range (A1:Y1).

• Go to Format Conditional Formatting
• Customize your conditional format rules as follows:
  • Under Format cells if select custom formula is (please see 1 in the picture below).
  • Copy the formula below and paste it into the box provided (please see 2 in the picture below).

=and(isblank(A1)=false,OR(weekday(A1)=7,weekday(A1)=1))

Click the fill color and choose red (please see 3 in the picture below), then click Done.

Here is the result:

How to highlight a list of holidays in a calendar range in Google Sheets

Suppose you want to highlight in purple a custom holiday on your calendar in January 2021. Lets follow the steps below:

• Prepare your own calendar in Google Sheets. You can read how to do this in our blog post about the weekly schedule template.

Pay attention here that:

• Your data range: A4:G9
• Holiday range: J4:J5

Then complete the usual flow:

• Go to Format Conditional formatting.
• Customize your conditional format rules as follows:
  • In the Conditional format rules box, Under Format cells if select custom formula is.
  • Copy this formula =IF(A4="",,match(A4,$J$4:$J6,0)) and paste in the box provided to highlight January 2021 Holidays.
  • Click the fill color and choose purple.
  • Click Done (please see picture below for more details).

Here is the result:

How to highlight the date if it falls between two dates

Suppose you want to highlight in green the date values in column A that fall between 2/2/2020 and 2/28/2020. Here are the steps:

• Select the range (A2:A9).
• Click Format Conditional formatting.
• Customize your conditional format rules as follows:
  • In the Conditional format rules box, Under Format cells if select is between. Two boxes will appear underneath. In the first box, enter this formula =date(2020,2,2); in the second box this formula =date(2020,2,28)
  • Click the fill color and choose green.
  • Click Done.

Here is the result:

How to highlight a column based on a date in another column

What if we have data in two columns, and want to highlight cells in column A if dates in column B are less than six months from today?

• Select the range (A2:B7).
• Click Format Conditional formatting.
• Customize your conditional format rules as follows:
  • In the Conditional format rules box, Under Format cells if select custom formula is.
  • Copy this formula =$B2<edate(today(),6) and paste in the Value or formula box.
  • Click the fill color and choose red.
  • Click Done.

How to highlight specific weekdays in Google Sheets

If you want to highlight specific weekdays in Google Sheets, for example,Tuesdays, in a cell/column/row/range, please use the WEEKDAY function as explained below:

• Select the range (B2:B7).

• Click Format Conditional Format
• In Format rules choose Custom formula.
• Customize your conditional format rules as follows:
  • Copy this formula =weekday(B2)=3 and paste into the value or formula box.
  • Choose which color you want to highlight the weekdays.
  • Click Done.

Here is the result:

Note: The values of WEEKDAY are as follows:

• 1 = Sunday
• 2 = Monday
• 3 = Tuesday
• 4 = Wednesday
• 5 = Friday
• 6 = Saturday

How to prevent Google Sheets from changing date format

Sometimes you face a situation where a string is being interpreted as a date. For example, you enter 2-2021, which is supposed to be an invoice number (plain text), but Google Sheets interprets it as 2-1-2021 automatically, as shown in the picture below:

Complete the following steps to preformat the cells, so that any numeric value you enter will not be automatically converted to be a date.

• Select the range (B2:B).
• Click the Format Number Plain text

How to automatically change format date in Google Sheets

To automatically convert a value to a date in your desired format, you can use the ARRAYFORMULA function as follows:

=arrayformula(IF(logical_test, [value_if_true], [value_if_false])).

• logical_test is a value or a given condition that is stored as Original Date-Time that can be evaluated as TRUE or FALSE.
• value_if_true is the value to return as a new date format (DD MMMM YYYY) when logical_test evaluates to TRUE.
• value_if_false is the value to return as blank when logical_test evaluates to FALSE.

This formula can change the original date format into your target format. All you need to do is prepare another column to store your target format and set up an ARRAYFORMULA formula there to convert the original date format into your target date format automatically. Here is an example:

Suppose we have the Original Date-Time format in column A. We want to automatically change values from this column into the DD MMMM YYYY format (02 January 2019). The converted values will be stored in column B titled Formatted Date Automatically.

We need to apply the ARRAYFORMULA formula in Column B, so whenever we input the new date format into column A, this formula will change it into a targeted format automatically and will be stored in column B. Here is what the formula will look like:

=arrayformula(if((A2:A)<>"",text(A2:A,"DD mmmm YYYY"),""))

Remember that this formula may ignore the specific input date format of the Original Date-Time column if the date values do not correspond to your spreadsheet location format. For example, if you use the UK location, the formula will ignore 21 01 2019, but it will work with 01 21 2019.

Now, whatever date we input into column A, it will automatically change into DD MMMM YYYY in column B. The result test is:

If the Google Sheets date format doesnt work

In some cases, you may find that Google Sheets date format is not compatible with your data. There are three reasons why this might be the case:

Incorrect syntax in your date functions

If you enter the parameters to your date function not in a numeric format, then it will return the #VALUE! error.

For details, letstake a look at an example a table containing Year in column A, Month in column B and Day in column C. We want to create a date format in MM/DD/YYYY (because we use the US location) using the DATE function combining Column A, B, and C using the syntax =date(year, month, day). The result is shown below:

When we enter 2001 for Year (A2), April for Month (B2), and 30 for Day (C2) using the formula =date(A2, B2, C2) into D2, the error #VALUE! is returned because the parameter 2 (Month) is not a number, but a string (April). Likewise, if we enter a string for Year or Day, #VALUE! will be returned as an error.

To fix this error, we should enter all values in numbers.

Year values are less than 0 or greater than 10,000

Google Sheets will return the #NUM! error value if you type the year as less than 0 or greater than 10,000.Here is an example of the DATE formula.

=DATE(-100,4,3)

The same error will occur if you use a year value greater than 10,000:

=DATE(10000000,4,5)

Your input date value does not comply with your Google Sheets location setting

The date value you input will be treated as textual values if they are not in the format of your Google Sheets location setting.

For example, European date format (dd/mm/yyyy) is not recognized by Google Sheets if your location is set to the United States. The solution is to use the American date format (mm/dd/yyyy).

Back to Blog