Date Formula in Google Sheets: Dates, Days, and Uses

The basic date formula in Google Sheets is =DATE(year, month, day). For example, with the year, month, and day entered as numbers in A2, B2, and C2, use =DATE(A2,B2,C2) to build a real date; Google Sheets can then format it, compare it, or use it in date calculations.

Which Google Sheets date formula should you use?

There is no single formula for every date task. Choose a function based on whether you need to construct a specific date, show the current date, do arithmetic, or extract information from a date. These are common starting points:

Use DATE to combine a year, month, and day into one date.
Use TODAY to return the current date, or NOW to return the current date and time.
Subtract one date from another to count elapsed days.
Use EDATE or EOMONTH for calculations by month, and YEAR, MONTH, or DAY to extract a component.

A date can appear as text or as a number with date formatting, and that difference matters. A genuine date value can be sorted chronologically and used in calculations; text that merely looks like a date may not behave the same way. When possible, keep dates as date values and apply a display format rather than converting them into text.

How the DATE function works

DATE takes three inputs in this order: year, month, and day. With no cell references—the inputs are the literal numbers 2025, 3, and 8—the formula =DATE(2025,3,8) returns March 8, 2025, as a date value. The formula uses commas between arguments in the examples here.

If your data is in cells, with the year in A2, month number in B2, and day number in C2, =DATE(A2,B2,C2) combines those values. For example, if A2 is 2025, B2 is 3, and C2 is 8, the result is March 8, 2025. This is useful when a spreadsheet stores date parts in separate columns or imports them separately.

DATE also normalizes values outside the ordinary month and day ranges. With no referenced cells and the literal inputs 2025, 13, and 1, =DATE(2025,13,1) returns January 1, 2026: month 13 rolls into the following year. This behavior can be helpful for date arithmetic, but it can also conceal bad source data, so validate inputs if month and day values are expected to stay within normal ranges. In Google Sheets, years from 0 through 1899 are interpreted by adding 1900; for instance, a year argument of 25 represents 1925, not 25 CE.

Show today’s date or the current time

TODAY returns the current date without a time. With no input cells, =TODAY() returns the date on which the sheet recalculates; the exact result changes over time, so it is not a fixed timestamp. It is useful for an aging report, a due-date check, or a dashboard that should always show the present date.

NOW returns the current date and time. With no input cells, =NOW() returns a date-time value that updates when the spreadsheet recalculates. The displayed time depends on the cell’s number format and the spreadsheet’s time zone. Neither function is a reliable way to record an unchanging event time: if you need a permanent entry, insert a fixed date or time rather than relying on a formula that can recalculate.

TODAY and NOW do not necessarily refresh at the exact stroke of midnight. Google Sheets recalculates formulas according to its calculation settings and spreadsheet activity. For a report where the date must remain fixed after it is created, consider entering a date value directly. In the web interface, you can set the spreadsheet’s time zone through File > Settings; this can affect the date and time displayed by current-date formulas.

Add days, compare dates, and count elapsed time

Google Sheets stores date values in a form that supports arithmetic. With a starting date in A2, the formula =A2+7 returns the date seven days after A2. If A2 contains March 8, 2025, the result is March 15, 2025. To calculate a date one week earlier, use =A2-7; with the same A2 value, that returns March 1, 2025.

To count elapsed days, put the earlier date in A2 and the later date in B2, then use =B2-A2. If A2 is March 1, 2025, and B2 is March 10, 2025, the result is 9 days. Format the result cell as a number if it appears as a date. This subtraction counts the difference between the dates, not both endpoints inclusively; a period that includes March 1 through March 10 contains 10 calendar dates.

For a count of workdays rather than calendar days, with a start date in A2, end date in B2, and optional holiday dates in D2:D10, use =NETWORKDAYS(A2,B2,D2:D10). It counts weekdays in the range and excludes listed holidays; if there are no holidays to exclude, the third argument can be omitted. This standard function uses a Monday-to-Friday workweek. For a schedule with a different weekend pattern, Google Sheets also provides NETWORKDAYS.INTL, which accepts a weekend setting.

Calculate by month or extract date parts

Adding 30 days is not the same as adding one calendar month. A month can have 28, 29, 30, or 31 days, so use a month-based function when the requirement is expressed in months. With a date in A2 and the number of months to add in B2, =EDATE(A2,B2) returns the date shifted by that many months. If A2 is January 31, 2025, and B2 is 1, the result is February 28, 2025; the target month does not have a January-style 31st day.

To find the last day of a month, with any date in A2 and zero in B2, =EOMONTH(A2,B2) returns the last day of A2’s month. For example, if A2 is February 12, 2025, and B2 is 0, the result is February 28, 2025. Set B2 to 1 to get the last day of the following month. These functions are useful for recurring schedules and month-end summaries, but check how an end-of-month date should roll forward in your particular workflow.

For a date in A2, =YEAR(A2), =MONTH(A2), and =DAY(A2) return its year, month number, and day number. If A2 is March 8, 2025, the results are 2025, 3, and 8. These numeric components can support grouping and filters, but a date value is generally more convenient when you need to sort, display, or calculate with full dates.

For whole months or years between two dates, Google Sheets includes DATEDIF. With the start date in A2 and the later date in B2, =DATEDIF(A2,B2,"M") returns the number of complete months between them; for January 15 and March 14 in the same year, it returns 1, not 2. The “M” unit counts completed months, so it does not simply subtract month numbers. DATEDIF also supports units such as “Y” for complete years and “D” for days. Keep the start date earlier than or equal to the end date.

Format dates so they are readable

A formula can return a correct date value while the cell displays a serial number or an unexpected date style. Select the cell or range, then choose Format > Number > Date in Google Sheets. For more control, use Format > Number > Custom date and time and select or assemble the desired display pattern. For instance, a date might display as 3/8/2025, 08-Mar-2025, or Saturday, March 8, 2025, depending on the chosen format and locale.

Formatting changes how a value looks, not the underlying date. If you need a date as text for a label or export, use TEXT. With a date value in A2, =TEXT(A2,"dddd, mmmm d, yyyy") returns text such as Saturday, March 8, 2025. Because the result is text, it is less suitable for later date calculations and may sort alphabetically rather than chronologically. Use it for presentation, not as a replacement for the original date value when calculations are still needed.

Google Sheets may interpret dates entered directly into cells according to the spreadsheet’s locale. The string 3/8/2025 can mean March 8 or August 3 in different regional conventions. Check File > Settings for the spreadsheet locale, and prefer unambiguous entries or DATE formulas when dates are being assembled from numeric parts. The display format and the locale used to interpret entered values are related, but they are not the same setting.

Common date formula problems and fixes

The result shows a number. The formula may have returned a valid date serial that is formatted as a plain number. Select the cell and choose Format > Number > Date.
A date is left-aligned or will not calculate. It may be stored as text. Check for an apostrophe, extra spaces, or a locale mismatch. Re-enter or convert the value into a date value before doing arithmetic.
The result is one day off. Check whether the calculation should include both the start and end dates, and confirm that the input values are actual dates rather than text. Simple subtraction gives elapsed days and does not count both endpoints.
The month-end result is unexpected. DATE and EDATE normalize dates when the target month is shorter. Decide whether your rule should use the last day of the month, then consider EOMONTH where appropriate.
The date changes when the sheet is opened later. TODAY and NOW are recalculating functions. Use a fixed entered value if the record must not change.

Also check the formula’s argument separators if it produces a parse error. The examples in this guide use commas, but spreadsheet locale settings can affect formula conventions in some environments. If a pasted formula is rejected, check the locale and the syntax expected by the sheet rather than changing the function name arbitrarily.

Choosing between a formula and a fixed date

Use a formula when the date should respond to source data or update automatically. DATE is appropriate for assembling components; TODAY is useful for a live current-date comparison; EDATE and EOMONTH handle calendar-month calculations. A formula is also easier to audit when its inputs are stored in separate cells and the result needs to update when those inputs change.

Use a fixed date when the value represents a historical event that must stay unchanged, such as the date an item was received or a task was completed. A TODAY formula in a completion-date column will eventually show a later date, not the original completion date. Keeping the entered value separate from calculated dates can prevent that type of recordkeeping error.

Excel also has DATE, TODAY, NOW, EDATE, and EOMONTH, so many basic formulas transfer between Google Sheets and Excel. However, menu names, locale behavior, and some spreadsheet features differ. The formatting instructions above are for Google Sheets in a web browser; in Excel, date formatting is accessed through its own ribbon and number-format controls.

Frequently Asked Questions

What is the formula for today’s date in Google Sheets?
Use TODAY in the cell where you want the current date to appear. It updates when the spreadsheet recalculates, so it is not a fixed record of the day on which a row was created.

How do I make a date from separate year, month, and day columns?
Use DATE with the year, month, and day cells in that order. This creates a date value from numeric components, which is safer for calculations than joining the components into a text string.

How do I add one month to a date?
Use EDATE with the date and a month offset of 1. If the original day does not exist in the target month, the result falls on that month’s last valid day; January 31 shifted one month becomes February 28 in 2025.

Why does my Google Sheets date formula show a number?
Dates are stored as numeric values, and the cell may be formatted as a number. Apply Format > Number > Date to display it as a date; the underlying value can still be used in calculations.

How do I count days between two dates?
Subtract the earlier date from the later date. The result is elapsed calendar days, so it does not count both the first and last day as included dates. Use NETWORKDAYS instead when you need weekdays and may need to exclude holidays.

Can I make a date stay fixed instead of updating?
Yes. Enter a fixed date value rather than using TODAY or NOW for a record that must not change. Current-date formulas are designed to recalculate, which makes them useful for live reports but unsuitable as permanent timestamps.

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.