How to Add Date in Google Sheets (Manual, & Smart Options)

Want to know how to add dates in Google Sheets?In this guide, you’ll learn different ways to add date in Google Sheets spreadsheet.

Dates are one of the most basic, yet a core feature of a well planned spreadsheet. They keep tasks on track, anchor reports, and drive automation in dashboards. Plus they also make your templates look pro, easy to use, and ready to understand.

In this guide, we’ll cover different ways you can insert dates in your Google sheet spreadsheet, including manual for easy entry, automatic dates that update, and interactive dropdowns that make data entry simple.

Also read how to design spreadsheets that you can sell on Etsy.

How to add dates manually in Google Sheets

Sometimes, when you’re working on simple spreadsheets, manual entry is efficient, fast, flexible, and perfect for fixed dates. You can add dates to your Google Sheet spreadsheet with just number formatting.

1. How to add date to spreadsheet with number formatting

This is probably the easiest method to insert dates in spreadsheets. Google Sheets basically treats dates like numbers, with proper formatting you can present numbers like dates.

1. Select the cells or column for dates.
2. Go to Format, Number, Date for a standard format.
3. For a custom look, pick Format, Number, Custom date and time.
4. Type in dates like 1/5/2025 or 2025-01-05. Sheets will convert number and display it as dates.

What to look for if your date is not displayed properly

• Set your file Locale in File -> Settings to match your audience’s region.
• If a date shows left-aligned, it is probably text. Remove any leading apostrophe.
• Keep date columns consistent. Mix of text and dates breaks sorting and formulas.

What are some common date formats you can use in your spreadsheets?

Your buyers might read dates differently. Use formats that feel obvious to them and reduce support questions.

Some common formats to style number as a date are:

• Short date for compact tables, like 1/5/2025.
• Long date for readable schedules, like Sunday, January 5, 2025.
• With time: use this in places where accurate time stamping is needed, like Jan 5, 2025 2:07 PM

Example: Enter 1/5/2025 in A2, then apply mmmm d, yyyy to display January 5, 2025 without changing the underlying value. It will display your date in selected format.

2. Using Keyboard Shortcuts for Quick Date Entry

If you’re working on a document and want to insert a date, you can do it with keyboard shortcut.

Common keyboard shortcuts to insert dates:

• Insert Current date: Ctrl + ;
• Insert Current time: Ctrl + Shift + ;
• Insert Date-time combo: press Ctrl + ;, space, then Ctrl + Shift + ;

How this helps:

• You can insert dates instantly using keyboard
• You avoid typos that break formulas and charts.

How to Add Dates in Google Sheets Automatically with Formulas

While inserting dates manually is good enough for simple spreadsheets, most require more complex needs.

In these cases, you can insert dates automatically to save time, educe errors, and keep templates updated for changing dates.

1. TODAY and NOW Functions for Real-Time Updates

Use these two functions when you want live dates.

• =TODAY() inserts the current date.
• =NOW() inserts the current date and time.

Step-by-step setup:

1. Click a cell where you want to insert today’s date
2. Type =TODAY() and press Enter.
3. Format the cell as a date in Format, Number, Date.
4. If you want to insert date + time, Use =NOW() , then format as Date time.

Where to use these methods:

• Dashboards: show “Updated on” with =TODAY() so buyers know the sheet is fresh.
• Creation date: place =TODAY() in a hidden setup sheet, then pull that cell into headers.
• Schedules: base rolling views on =TODAY() to highlight tasks due this week.

One thing to keep in mind when using Today() or Now() methods:

TODAY and NOW recalculate when the file opens, when you edit. If you don’t want the date to change, then you must freeze it.

2. How to create a rolling list of dates:

You can create a dynamic list of dates for any number of days by using the following formula:

Insert below formula in the starting cell:

=SEQUENCE(7,1,TODAY(),1) 

It will insert date in the below cells, you can format the subsequent cells as Date format for proper display.

How to Add Date Calendar Dropdown in Google Sheets

If you want to give your users to select any date from a calendar, then drop downs is the best choice. It removes typos, keep formulas safe, and make your file feel polished.

1. Creating a Simple Date Calendar drop down

Follow below steps to create a calendar dropdown in Google Sheets spreadsheet.

1. Go to cell where you want to insert the date
2. Go to Data -> Data Validation -> Add Rule
3. From the criteria, select Is Valid Date
4. Click Done
5. Now, when you click on the cell, you will get a calendar dropdown where you can select the date
6. Change the format of the date to give it desired appearance

2. Create dropdown of dates from another column

You can create a dropdown of dates already present in another column. For this, you need to build the list of valid dates. And then, validate the input cells to use that list.

Follow these steps to create a dropdown of dates present in another column:

1. Enter your allowed dates in a helper range, like Sheet1!A1:A40. Keep them formatted as dates.
2. Select the input cells where users will choose a date.
3. Go to Data -> Data validation.
4. For Criteria -> choose List from a range -> then select your date list range, for example, select Sheet1!A2:A40.
5. Check Show dropdown list in cell. Set On invalid data to Reject input to block mistakes.
6. Click Done.

Benefits of this approach:

• It forces clean entries that your formulas can trust.
• It keeps sorting, filtering, and date math stable.
• You can format the list range to control how dates display in the dropdown.

You can also use PLR template to speed up this process as most of the PLR templates already have dates.

Frequently Asked Question when inserting dates in Google Sheets

Here are the most common date issues and fixes that can help to troubleshoot date issue properly.

Why is my date left aligned or not recognized?

Left aligned usually means text, not a real date.

• Remove a leading apostrophe if you see one.
• Set the column format to Date first, then re-enter or paste values.
• You can use the =Datevalue(A2) method to convert text to date, where A2 contains a valid text date.
• Fix locale issues in File, Settings, Locale so 1/5/2025 vs 5/1/2025 parses correctly.

How do I stop TODAY or NOW from changing?

=TODAY() and =NOW() update when the sheet recalculates. You can lock them to prevent them to change values:

• Copy the cell, then Paste special, Values only.
• Use a helper cell like Z1 with =TODAY(), reference $Z$1 everywhere, then replace Z1 with the fixed value when ready.

How do I auto-fill a series of dates fast?

You can use formula to auto-fill a series of dates quickly:

Method 1:

• Enter date in cell A2
• Drag the fill handle from a start date to create a daily series.

Method 2:

• Enter date in cell A2
• Generate a list with =SEQUENCE(7,1,TODAY(),1) for 7 days from today.

How to add months to a date

Use the below formula, if you want to add months to a given date:

• Enter date to which you want to add months in cell A1
• Enter this formula in cell A2:  =EDATE(A1,1) 

A2 will now display date entered in A1 + 1 month

How to add workdays to date

If you’re creating a planner, where you want to display only workdays, then you can use below formula:

• Enter date to which you want to add workdays in cell A1
• Enter this formula in cell A2:  =WORKDAY(A2,10) 

This will add 10 working days to the date in A1

How do I add a clickable date picker?

Data validation is the easiest way to add a calendar pop-up.

• Select the input cells.
• Go to Data, Data validation.
• Choose Criteria, Date, then set a condition or Any valid date.
• Check Show dropdown list in cell to display the picker.

Can I make a date drop down with specific allowed dates?

Yes. First build a list, then use it as data validation.

• Put allowed dates in a helper range, formatted as dates.
• GO to Data -> Data validation -> Criteria -> List from a range, then select your list.
• Set On invalid data to Reject input.

How do I limit dates to a range, like this month only?

In order to limit dates to a specific range, we will again use data validation.

• Go to Data -> Data Validation -> Criteria -> Date, between, start and end.
• Use =EOMONTH(TODAY(),-1)+1 for the first day of this month.
• Use =EOMONTH(TODAY(),0) for month end.

Users can only pick dates inside the window.

How do I handle workdays and holidays?

Use workday functions and a holiday list.

• =WORKDAY(start, days, holidays_range) skips weekends and listed holidays.
• Custom weekends: =WORKDAY.INTL(start, days, “0000011”, holidays_range) where 1 means weekend, 0 means workday.

Keep holidays on a dedicated sheet so buyers can change them without breaking formulas.

How do I convert messy text to real dates in bulk?

Try these steps:

1. Pre-format the column as Date.
2. Use Data, Split text to columns if dates include extra text.
3. Use =DATEVALUE(A2) when the text looks like a date but is stored as text.
4. For yyyy-mm-dd text, =VALUE(A2) often works since Sheets recognizes ISO.

where A2 is already a valid date value.

Why are my date formulas off by a day?

These are often caused by time zones isuses. You can fix it by setting proper locale for your Google Sheets.

Go to File -> Settings -> Time zone -> select your time zone. If you import data with times, apply a Date format or round down with =INT(A2) to drop the time portion.

How do I display week numbers or the week start?

Use following formulas to display week numbers:

• Week number: =WEEKNUM(A2,2) where 2 means weeks start on Monday.
• Start of the week: =A2-WEEKDAY(A2,2)+1.
• End of the week: =A2-WEEKDAY(A2,2)+7.

What is the safest way to do “date add Google Sheets” math?

You can use date functions to safely and properly add dates.

• Enter date in cell A2
• Add 7 days to a date: =A2+7
• Add months: =EDATE(A2,1) 
• Jump to month end: =EOMONTH(A2,1)
• Add 10 working days to the date: =WORKDAY(A2,10)

These will add dates properly without errors.

How do I sort dates correctly when some are text?

You will have to fix the text first before you can sort as dates.

• Convert text with =DATEVALUE() or =VALUE() as needed.
• Paste special, Values only to replace old text.
• Sort the column, A to Z.

If sort still looks wrong, check the Locale and reapply a date format

Where to learn more on how to create and sell spreadsheets?

Want a shortcut to sell spreadsheets? Check out Savvy Smart Spreadsheet course on how you can create passive income selling Google Sheet spreadsheet templates.

Next Steps

Hope this helps you to add dates in your Google Sheets spreadsheets. Try these tips today, then share what worked for you in the comments.


Spreadsheets are some of the most popular digital products to sell on Etsy. Download this Done-For-You spreadsheet template that you can customize and sell in your store.