How to Insert Date in Excel Formula (8 Ways)

Excel can store date values as a different category of its own. Dates starting from 1st January 1990 follows this pattern. You can write dates manually using a slash(/) in-between days, months, and years. But this article will focus on how to insert date in Excel formula.

Download Practice Workbook

Download the workbook with all the examples used in this demonstration, separated by spreadsheets, from below.

8 Ways to Insert Date in Excel Formula

There are so many methods to write down dates in an Excel cell. Below I will be using different methods to fill out the following table with dates. I will use different values that will come out of different methods as some have a strict output range.

1. Insert Current Date Using Shortcut

Microsoft Excel provides a keyboard shortcut to insert the current date effortlessly. This method is especially helpful in scenarios where you just have to input the current date as a value in a long-range of cells.

To do this, just select the cell you want to insert the date in and press ‘Ctrl+;’ on your keyboard. The date will automatically pop up. Press ENTER to take the value.

Read More: How to Combine Date and Time in One Cell in Excel (4 Methods)

2. Using DATE Function in Excel

There is the DATE function to write down different dates. It takes three arguments- year, month, and day (all in numbers). Follow the steps for a more detailed guide.

Steps:

• First, select the cell where you want to insert the date.
• Write down the following formula in the set.

Here I have used these values to insert the date 5th April 2022.

• Now, press Enter on your keyboard.

• Fill out the rest of the cells according to your values.

3. Insert Dynamic Date Using TODAY Function

There is another function called the TODAY function to insert date. Unlike the DATE function, you can only get values of the day you are inserting the value. The function doesn’t take any arguments either. The output of this function is dynamic, meaning you will see the value changed to the date you are viewing the spreadsheet if you reopen it some other day.

To use insert values with this function, simply follow these steps.

Steps:

• First, select the cell where you want to insert your date.
• Then write down the following function:

• Now, press Enter.
• You will have your current date inserted as a value in the cell.

Read More: How to Change Dates Automatically Using Formula in Excel

4. Static Date Using TODAY Function

The return value obtained from using the TODAY function is dynamic, meaning the date will change every day and will show you the current date of the day you are viewing the spreadsheet. But if you want to fix it to the value you insert date to by using the Excel function TODAY, you need to follow these steps.

Steps:

• First, get values from the TODAY function shown in the method above.
• Then select all the cells with the value you want to be static.

• Copy the cells by pressing Ctrl+C on your keyboard.
• Then right-click on the cell your range starts.
• Select Values(V) in the Paste Options from the menu.

Now your date values will turn static and always remain the same, no matter the day you are reviewing it.

5. Auto Insert Successive Date

For a long range of cells, if the date values follow a definite sequence, you can easily auto-insert them into the range. In this method, I am going to show you how to auto-insert dates if they come one after the other.

Steps:

• First, select a cell and manually fill out the date.

• Click the Fill Handle Icon and drag it down to the end of the table. You will have the cells filled up by the dates that follow the first cell.

Read More: Excel Automatically Enter Date When Data Entered (7 Easy Methods)

Similar Readings

• How to Display Day of Week from Date in Excel (8 Ways)
• Perform Automatic Date Change in Excel Using Formula
• Automatically Enter Date When Data Entered in Excel (2 Ways)
• How to Insert Dates in Excel Automatically (4 Simple Tricks)

6. Fill-Up Date with Intervals

If you want to fill up a range of cells with dates, but the values differ by some other number rather than one, this method will be particularly helpful for you. Follow the steps for a more detailed guide.

Steps:

• Select a cell and manually fill out the date.

• Now, right-click the Fill Handle Icon and drag it down to the end of the range.
• Then release the right-click button.
• Upon release, a context menu will appear. Select Series from it.

• Select the correct Date unit and Step value you want to jump from.

• Click on OK. You will have your dates filled up with the desired interval you wanted.

Read More: How to Enter Time in Excel (5 Methods)

7. Insert Random Dates

If you want to randomly insert a date for a range of cells, a combination of Excel formula of the RANDBETWEEN and DATE functions might be of help.

The RANDBETWEEN function takes two arguments- the start and end value between which it will random. The DATE function takes a year, month, and day as arguments and returns those in date format.

For the following steps, I am using the Excel formula for random dates between 9th September 2021 and 5th April 2022.

Steps:

• First, select the cell you want to insert the date in.
• Write down the following formula:

• Now press Enter on your keyboard.

• After that, click and drag the Fill Handle Icon to fill up the rest of the range with the formula.

Notice that, the value in the first cell has changed after filling up the range. This formula creates a dynamic value and changes every time you perform an operation on a cell. To make this static, you can copy and paste the values on top of the range as shown in the fourth method.

8. Other Functions to Use for Dates

There are other helpful functions when it comes to writing dates. The functions can be used to extract information from a date or to change the format of a date. I have added those methods here for a short read. If you want to learn more about the functions, go to the links attached.

For a demonstration of these methods, I will be using the following dataset.

8.1 Extract Number of Days Using DAY Function

The DAY function can be used to extract the day number of the month in the date.

• Select the cell and write down the following formula:

• Press Enter on your keyboard.

• Click and drag the Fill Handle Icon to fill up the rest of the cells.

8.2 Extract Weekday from Date

If you want to know which weekday it was on the mentioned date, the WEEKDAY function will be of use to you.

Steps:

• Select the cell and write down the following function:

• Now press Enter.

• Click and drag the Fill Handle Icon to fill out the rest of the range.

8.3 Extract Month from Date

Similarly, you can extract the months from the date using the MONTH function.

Steps:

• Select the cell and write down the formula:

• Now press Enter and you will have the month of the date.

• Click and drag the Fill Handle Icon to fill out the rest of the range.

8.4 Extract Year Using YEAR Function

To extract the year from a date, you can use the YEAR function. Simply follow these steps:

Steps:

Select the cell and write down

• Now press Enter. You will have the year of the selected date.

• Click and drag the Fill Handle Icon to fill out the rest of the range with the formula.

8.5 TEXT Function to Reformat Date

You may want to insert date in a different format or change the format of date already written. an Excel formula consisting of the TEXT function can be useful.

The TEXT function takes two arguments- a string of text and a pattern for format text.

Follow these steps to see how this function works with inserting date.

Steps:

• Select the cell and write down the following formula:

• Now press Enter on your keyboard. You will have the date formatted in the cell.

• Click and drag the Fill Handle Icon to fill up the rest of the range with the formula.

Read More: Formula for Weekly Dates in Excel (5 Examples)

Conclusion

These were the different methods you can use to insert date in Excel. Hope you found this guide helpful and easy to read. If you have any questions or suggestions, feel free to let us know below. For a more helpful and detailed guide visit Exceldemy.com.

Related Articles

• How to Insert Last Saved Date in Excel (4 Examples)
• Insert Drop Down Calendar in Excel (With Quick Steps)
• How to Insert Date in Footer in Excel (3 Ways)
• Insert a Date Picker in Excel (With Step-by-Step Procedure)
• How to auto populate date in Excel when cell is updated
• Insert Date in Excel That Updates (5 Easy Methods)
• Excel Macro: Insert Date and Time in a Cell (4 Examples)