The fastest way to enter a date

Type the date in the format your region uses — for the United States, that's usually month/day/year, like 3/15/2024. Google Sheets recognizes it as a date and formats it automatically. You can also type it as 3-15-2024 or March 15, 2024, and Sheets will convert it to the same format.

If you want today's date without typing it manually, click the cell where you want it and press Ctrl+; on Windows or Cmd+; on Mac. Sheets fills in the current date when ready. This is useful when you're logging when something happened or when a task was completed.

For the current date and time together, use Ctrl+Shift+; on Windows or Cmd+Shift+; on Mac. The timestamp updates only when you first enter it — it won't change every time you open the sheet.

Key Takeaways

  • Type dates in your region's standard format (3/15/2024 for the US) and Google Sheets recognizes them automatically.
  • Use Ctrl+; (Windows) or Cmd+; (Mac) to insert today's date without typing it.
  • The DATE function lets you build dates from separate year, month, and day values in other cells.
  • Change how a date looks by selecting the cells and using Format > Number > Date to pick from preset styles or create your own.
  • TODAY() and NOW() functions pull the current date or date-time, useful for calculations like counting days until a important date.

Using the DATE function to build dates from parts

If your year, month, and day are in separate columns — for example, year in column A, month in column B, day in column C — use the DATE function to combine them. Click the cell where you want the complete date and type =DATE(A1,B1,C1), replacing A1, B1, C1 with the cells that hold your year, month, and day.

The DATE function always expects the order: year first, then month, then day. So =DATE(2024,3,15) creates March 15, 2024. This is useful when you're importing data from another source where the date parts are split across columns, or when you're building dates from user input in a form.

You can also use DATE with numbers you type directly. =DATE(2024,3,15) works the same way as =DATE(A1,B1,C1) if A1 contains 2024, B1 contains 3, and C1 contains 15. Mix and match — =DATE(A1,3,15) uses the year from A1 but hardcodes the month and day.

Formatting dates to look the way you want

Google Sheets applies a default date format based on your region, but you can change how any date looks. Select the cells with dates, then click Format in the menu, choose Number, and select Date. A list of preset formats appears — click the one you want, like "Mar 15, 2024" or "15/03/2024".

If none of the presets match what you need, scroll to the bottom of that same menu and click More date and time formats. A dialog opens where you can build a custom format. For example, type mmmm d, yyyy to get "March 15, 2024", or yyyy-mm-dd to get "2024-03-15". The letters tell Sheets what to show: m for month, d for day, y for year. Repeat them to change how they display — mm gives "03", m gives "3".

The format you choose only changes how the date looks on screen. The actual date value stays the same, so formulas that use those cells still work correctly.

Using TODAY() and NOW() to work with current dates

The TODAY() function returns today's date. Type =TODAY() in any cell and it shows the current date. Unlike the keyboard shortcut, TODAY() updates automatically every time you open the sheet — useful for tracking how long something has been in progress or how many days remain until a important date.

The NOW() function works the same way but includes the time as well. =NOW() shows the date and current time. Both functions take no arguments — you don't put anything inside the parentheses.

You can use TODAY() in math. For example, =TODAY()-A1 subtracts the date in A1 from today, giving you the number of days between them. =A1-TODAY() does the reverse. This is how you count days until a important date or days since something started.

Calculating the difference between two dates

Subtracting one date from another gives you the number of days between them. If A1 holds a start date and B1 holds an end date, type =B1-A1 in a new cell. The result is the number of days from start to end.

If you want the result in a different unit — weeks, months, or years — use the DATEDIF function. Type =DATEDIF(A1,B1,"D") to get days, =DATEDIF(A1,B1,"M") for complete months, or =DATEDIF(A1,B1,"Y") for complete years. The letter in quotes tells Sheets which unit to use. "D" counts days, "M" counts full months (so March 1 to March 15 is 0 months), and "Y" counts full years.

DATEDIF is stricter than subtraction. If you subtract March 1 from March 15, you get 14 days. DATEDIF with "M" returns 0 because a full month hasn't passed. Use subtraction for straightforward day counts and DATEDIF when you need to count by calendar units.

Adding or subtracting days from a date

To add days to a date, use straightforward addition. If A1 holds a date and you want to add 7 days, type =A1+7. If you want to add 30 days, type =A1+30. Sheets treats dates as numbers internally, so adding a number to a date shifts it forward by that many days.

Subtraction works the same way. =A1-7 removes 7 days from the date in A1. This is useful for setting reminders — if a important date is in A1, you might create a reminder date with =A1-3 to alert yourself three days before.

You can also add or subtract time using decimals. Since Sheets treats a full day as 1, half a day is 0.5. So =A1+0.5 adds 12 hours to a date that includes time. For most spreadsheets tracking events by day, straightforward whole numbers are enough.

Common date problems and how to fix them

If you type a date and Sheets treats it as text instead of a date, the cell shows the text left-aligned instead of right-aligned. This happens when the column is formatted as text before you enter the date. Select the cell, go to Format > Number > Automatic, then re-enter the date. Sheets should recognize it this time.

If a date formula returns a number like 45000 instead of a date, the cell is formatted as a number. Select it, go to Format > Number > Date, and pick a date format. The number converts to a readable date when ready. This often happens when you use DATE() or TODAY() in a new cell that hasn't been formatted yet.

If you're copying dates from another source and they don't import correctly, paste them as plain text first, then use the DATEVALUE() function to convert them. Type =DATEVALUE(A1) where A1 holds the text that looks like a date. Sheets converts it to a real date you can use in formulas.

Frequently Asked Questions

Can I set a date to automatically update to tomorrow each day?

No, but you can use =TODAY()+1 to show tomorrow's date, and it updates automatically every day. If you want a date that never changes, use the keyboard shortcut Ctrl+; (Windows) or Cmd+; (Mac) instead — that locks in today's date and doesn't update.

What's the difference between typing a date and using the DATE function?

Typing 3/15/2024 is faster for one or two dates. The DATE function is better when you're building dates from data in other cells or when you need to create many dates at once. Both create the same date value — the difference is just how you enter it.

How do I show only the month and year, not the day?

Select the cells with dates, go to Format > Number > More date and time formats, and type mmmm yyyy for "March 2024" or mm/yyyy for "03/2024". The format changes how it looks without changing the actual date.

Why does my date show as a number when I paste it from another spreadsheet?

Different spreadsheet programs store dates differently. Paste the data, select the cells, go to Format > Number > Date, and choose a format. If that doesn't work, use =DATEVALUE(A1) to convert the number to a date Sheets recognizes.

Can I calculate how many weeks are between two dates?

Divide the day count by 7. If A1 and B1 hold two dates, type =(B1-A1)/7 to get weeks. For complete weeks only, use =INT((B1-A1)/7) — INT rounds down to whole weeks. DATEDIF doesn't have a weeks option, so division is the standard approach.