Date in Google Sheets: Enter, Format, and Calculate

To enter a date in Google Sheets, type it into a cell in a format your spreadsheet recognizes, such as 11/3/2026, then choose Format > Number > Date if you want to change how it looks. Sheets stores recognized dates as numeric values, so you can sort them, compare them, and use them in calculations—not just display them as text.

How dates work in Google Sheets

A date that Sheets recognizes is a number representing a day on its calendar. The cell’s number format controls whether that value appears as 11/3/2026, Nov 3, 2026, or another date style. Changing the format changes the display, not the underlying date. This is why the same date can look different in two cells while still behaving the same in calculations.

It also explains a common surprise: a date may appear as a large number if its cell is formatted as a plain number. The underlying value is still there; change the format to a date to display it conventionally. Conversely, text that merely looks like a date is not necessarily a date value. Text dates can fail to sort chronologically or produce errors when used in date arithmetic.

Google Sheets uses a date-number system based on days from December 30, 1899. Times are represented as fractions of a day, so noon is one-half of a day. A cell can therefore hold both a date and a time, even when its current format shows only the date. This shared numeric foundation makes date comparisons and calculations possible.

Enter dates and choose a display format

In a Google Sheets spreadsheet opened in a web browser, select a cell and type a date. Common entries include 11/3/2026, 3-Nov-2026, or November 3, 2026. Which forms Sheets recognizes can depend on the spreadsheet’s locale, so check the result rather than assuming that a short numeric date means the same thing everywhere.

To set a date format, select the relevant cells and use Format > Number > Date. For more control, open Format > Number > Custom date and time, choose or assemble a pattern, and apply it. Examples include a month name with a four-digit year, or a compact day-month-year display. A custom format affects appearance; it does not rewrite the stored date.

For a quick fixed entry, Google Sheets also supports a keyboard shortcut to insert the current date: Ctrl+; on Windows or ChromeOS, and ⌘+; on Mac. This enters a date value in the selected cell; it does not keep updating like a live current-date formula.

Set the locale to avoid ambiguous dates

A date such as 04/05/2026 can mean April 5 or May 4, depending on the date convention. To check or change the spreadsheet’s regional settings, go to File > Settings and review the Locale setting. The locale affects how dates and numbers are interpreted and displayed across the spreadsheet. It is a spreadsheet-level setting, so changing it can affect other users and existing entries.

If a file is shared internationally, avoid relying on ambiguous numeric dates in instructions or imported data. Use a clear month name when entering dates by hand, or build a date from separate year, month, and day values. After changing the locale, review a sample of important dates: a different display may be expected, but ambiguous text may also be interpreted differently than intended.

Build reliable dates with formulas

The DATE function creates a date from numeric year, month, and day inputs. For example, with the year in A2, month number in B2, and day number in C2, enter =DATE(A2,B2,C2) in D2. If A2 is 2026, B2 is 11, and C2 is 3, the result is November 3, 2026. Format D2 as a date if it displays as a number.

DATE also normalizes values beyond the usual range: a month number greater than 12 carries into the following year, and a day beyond the month’s length carries forward. That can be useful in deliberate calculations, but it can also hide bad source data. If users enter dates into separate fields, add validation or checks rather than assuming every combination represents the intended day.

For a date that updates automatically, use TODAY. In a sheet where you want the current date displayed, enter =TODAY() in the chosen cell. It returns the current date when the spreadsheet recalculates; it is not a timestamp that permanently records when someone made an entry. For the current date and time, =NOW() returns a date-time value that can also recalculate. Use a typed date or the keyboard shortcut instead when a fixed record is needed.

Calculate with dates

Once dates are recognized values, ordinary comparisons and arithmetic can answer practical questions. Subtracting an earlier date from a later one returns the number of days between them. Functions can also extract a day, month, or year, move a date by months, or count working days. Format results according to what they represent: a duration in days should generally be displayed as a number, not as a calendar date.

For a spreadsheet with a date in A2, =EOMONTH(A2,0) returns the final day of the same month. If A2 contains November 3, 2026, the result is November 30, 2026. Changing the second argument to 1 returns the final day of the next month. This is useful for month-end reporting, but the result still needs an appropriate date format.

To count elapsed days between two dates, with a start date in A2 and an end date in B2, enter =B2-A2. If A2 is November 3 and B2 is November 10 of the same year, the result is 7. This counts the difference between the dates; it does not count both endpoints as separate days. An inclusive count for a range would add one, provided that is the counting rule the task requires.

For a readable label, with a date value in A2, use =TEXT(A2,"mmmm d, yyyy") to return text such as November 3, 2026. The result is text, not a date value, so it is convenient for labels but usually less suitable for later date calculations or chronological sorting. Keep the original date in its own cell if you need both a human-friendly label and a usable date.

Sort, filter, and validate date entries

Recognized dates sort in calendar order because Sheets can compare their underlying values. To sort a table, select the table and use Data > Sort range, choosing the date column and the desired order. Include the related rows in the range so names, amounts, and other details remain attached to the correct dates. For ongoing tables, a filter can make it easier to show a chosen date range without rearranging the source data.

To limit mistakes during entry, select the cells and choose Data > Data validation. Configure the rule to accept a valid date or a suitable date condition, then decide whether invalid entries should be rejected or accompanied by a warning. Validation helps prevent some errors, but it does not replace checking imported information: a source may contain dates stored as text, blanks, or values that were interpreted under a different locale.

Fix dates that are stored as text

If a date will not sort correctly or date arithmetic fails, first check whether Sheets recognized it. Select the cell and inspect its displayed value and number format. Try applying Format > Number > Date. If the appearance changes into a date, the underlying value was numeric. If it remains unchanged or does not behave like a date, it may be text or an invalid entry.

For pasted or imported data, the problem often comes from a mismatch between the source’s date convention and the spreadsheet locale. A column containing both recognized dates and text can also look uniform while behaving inconsistently. Check a few entries, confirm the source format, and use a consistent conversion method before relying on sorting or formulas. Do not convert a column blindly if its values are ambiguous; verify whether, for example, 04/05 means April 5 or May 4.

Another frequent issue is seeing a number instead of a date. Select the cell and apply a date format. If a formula returns an unexpected date, inspect its inputs and whether the cells contain real dates, rather than changing the display until it merely looks right. Formatting cannot repair an incorrect underlying value.

Dates in Google Sheets versus Excel

Google Sheets and Microsoft Excel both use numeric date values and familiar functions such as DATE and TODAY, so many basic date calculations transfer between them. Their interfaces are different: the steps here refer to Google Sheets in a web browser, including its Format > Number and File > Settings menus, not Excel’s ribbon.

Moving a spreadsheet between products can still reveal differences in locale settings, custom formats, recalculation behavior, or how imported text is interpreted. Check important dates after conversion, especially in shared or business-critical files. A display that looks familiar is not proof that the underlying values were imported correctly.

Frequently Asked Questions

Why does Google Sheets show a number instead of a date?
The cell may be formatted as a number or plain text. Select it and try Format > Number > Date. If that does not make it a date, check whether the entry is text or was interpreted incorrectly.

How do I enter today’s date without it changing later?
Use Ctrl+; on Windows or ChromeOS, or ⌘+; on Mac, to insert a fixed date value. =TODAY() is different: it returns the current date and can update when the spreadsheet recalculates.

How do I change the date from month/day/year to day/month/year?
Select the cells and choose Format > Number > Custom date and time to change their display pattern. If Sheets is interpreting newly entered dates in the wrong order, check the spreadsheet locale at File > Settings as well.

Can Google Sheets calculate the number of days between two dates?
Yes. With the earlier date in A2 and the later date in B2, =B2-A2 returns the elapsed number of days. For a November 3 start and November 10 end in the same year, the answer is 7 days.

Why will my date not sort in chronological order?
Some entries may be text rather than recognized date values, or the source dates may have been interpreted using the wrong locale. Check the entries and their formats, confirm the intended day and month, and convert the values consistently before sorting.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.