How to Insert Date in Excel Formula (8 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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


How to Insert Date in Excel Formula: 8 Quick Ways

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.

How to Insert Date in Excel Formula


2. Using the 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.

=DATE(2022,4,5)

How to Insert Date in Excel Formula

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.

How to Insert Date in Excel Formula


3. Insert Dynamic Date Using TODAY Function

There is another function called the TODAY function to insert a 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:
=TODAY()
  • Now, press Enter.
  • You will have your current date inserted as a value in the cell.

How to Insert Date in Excel Formula


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 where 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.

How to Insert Date in Excel Formula

Read More: How to Insert Current Date in Excel


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.

How to Insert Date in Excel Formula

  • 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.

How to Insert Date in Excel Formula


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.

How to Insert Date in Excel Formula

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

How to Insert Date in Excel Formula


7. Insert Random Dates

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

The RANDBETWEEN function takes two arguments- the start and end values between which it will be 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:

=RANDBETWEEN(DATE(2021,9,3),DATE(2022,5,4))

How to Insert Date in Excel 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.

How to Insert Date in Excel 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.

🔍 Breakdown of the Formula

=RANDBETWEEN(DATE(2021,9,3),DATE(2022,5,4))

👉 DATE(2021,9,3) and DATE(2022,5,4) returns the two dates of 9th September 2021 and 5th April 2022.

👉 RANDBETWEEN(DATE(2021,9,3), DATE(2022,5,4)) random the numbers between the numbers the date represents and then return it in date format to give us a random date.


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:
=DAY(C5)

How to Insert Date in Excel Formula

  • Press Enter on your keyboard.

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

How to Insert Date in Excel Formula


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:
=WEEKDAY(C5)

How to Insert Date in Excel Formula

  • Now press Enter.

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

How to Insert Date in Excel Formula


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:
=MONTH(C5)

How to Insert Date in Excel 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.

How to Insert Date in Excel Formula


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 it down

=YEAR(C5)

How to Insert Date in Excel Formula

  • 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.

How to Insert Date in Excel Formula

Read More: Automatically Enter Date When Data Entered in Excel


8.5 TEXT Function to Reformat Date

You may want to insert the date in a different format or change the format of the 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 a date.

Steps:

  • Select the cell and write down the following formula:
=TEXT(C5,"dd mmmm,yyyy")

How to Insert Date in Excel 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.

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.


Conclusion

These were the different methods you can use to insert dates 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.


Related Articles


<< Go Back to Insert Date | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy, holding a B.Sc. in Naval Architecture and Marine Engineering, has contributed to Exceldemy for nearly 1.5 years. As a leader in Excel, VBA, and Content Development teams, he authored 114+ articles and assisted the Exceldemy forum. Presently, as a project writer, he prioritizes stepping out of his comfort zone, aiming for constant technical improvement. Niloy's interests encompass Excel & VBA, Pivot Table, Power Query, Python, Data Analysis, and Machine Learning libraries, showcasing his commitment to diverse... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo