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.
Related read: how to design spreadsheets that you can sell on Etsy.
Key Takeaways
- Use Data validation to add a clickable calendar dropdown. This is probably the easiest way to add date calendar
- Format numbers as dates for simple manual entry
- Use formulas like Today(), Now(), and Sequence() to add date automatically
- Bonus: use conditional rules to highlight specific dates
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.
- Go to cell where you want to insert the date
- Go to Data -> Data Validation -> Add Rule
- From the criteria, select Is Valid Date
- Click Done
- Change the format of the date to give it desired appearance
Now, when you click on the cell, you will get a calendar dropdown where you can select the date.
2. Create dropdown of dates from another column
The above method works if you want to show calendar dropdown in a cell. But if you want to show dates from another column then follow this method.
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:
- Enter your allowed dates in a helper range, like Sheet1!A1:A40. Keep them formatted as dates.
- Select the input cells where users will choose a date.
- Go to Data -> Data validation.
- For Criteria -> choose List from a range -> then select your date list range, for example, select
Sheet1!A2:A40. - Check Show dropdown list in cell. Set On invalid data to Reject input to block mistakes.
- 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.
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.
- Select the cells or column for dates.
- Go to Format, Number, Date for a standard format.
- For a custom look, pick Format, Number, Custom date and time.
- 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.
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:
- Click a cell where you want to insert today’s date
- Type
=TODAY()and press Enter. - Format the cell as a date in Format, Number, Date.
- 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.
Limitation to keep in mind when using Today() or Now() methods:
TODAY and NOW recalculate when the file opens or when you edit. If you don’t want the date to change, then you must freeze it. Otherwise it will change to current Date or time.
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.
Add conditional formatting for dates to highlight what matters:
Google Sheets shines in formatting and making spreadsheets look gorgeous. You can use conditional formatting to highlight specific dates to bring them to user’s notice.
- Select your date range in cell A2
- Go to Format, Conditional formatting.
- Use Custom formula: =A2<TODAY() if your dates start in A2.
- Choose bold colors for overdue, and softer colors for upcoming.
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.
Here are some troubleshooting tips if date is not being recognized:
– Remove a leading apostrophe if you see one.
-Set the column format to Date first, then re-enter or paste values.
– Fix locale issues in File, Settings, Locale so 1/5/2025 vs 5/1/2025 parses correctly.
=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.
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.
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
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
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.
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.
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.
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.
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.
Free guide & PLR Template
Turn Spreadsheets into Sales – Without the Overwhelm!
Get step-by-step guide and a FREE Google Sheet template you can customize & sell in your store today.
