How to Copy Same Date in Excel (6 Simple Methods)

Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, I am going to show you 6 simple methods on how to copy the same date in Excel. You can use these methods even in large datasets to make copies of a single date value in other adjacent cells. Throughout this tutorial, you will also learn some important Excel tools and techniques which will be very useful in any Excel related task.


How to Copy Same Date in Excel: 6 Simple Methods

We have taken a concise dataset to explain the steps clearly. The dataset has approximately 7 rows and 2 columns. Initially, we are keeping all the cells with dates in Date format. For all the datasets, we have 2 unique columns which are Sales Person and Joining Date. Although we may vary the number of columns later on if that is needed.

how to copy same date in excel


1. Using Fill Handle

The Fill Handle in Excel gives us the option to extend numbers, dates, or texts to other adjacent cells. Let us see how to use this tool to copy the same date to other cells.

Steps:

  • First, go to the lower-right corner of cell C5 and drag the + symbol down using the mouse.

using fill handle to copy the same date in excel

  • Now, click on the Auto Fill Options icon and select Copy Cells.

  • As a result, this will copy the date value of cell C5 to all the cells below.


2. Utilizing Absolute Reference

In this method, we will be using the Absolute Reference formula to copy the same date in Excel. Follow the steps below to achieve this.

Steps:

  • To begin with, navigate to cell C5 and insert the following formula:
=$C$5

using absolute reference to copy the same date in excel

  • Finally, press Enter and copy this formula down to show the same date in the remaining cells.

Read More: How to Use IF Formula with Dates


3. Applying Paste Special

The Paste Special feature also gives us a quick and easy way to copy the same date value to adjacent cells in Excel. Below are the steps to do this.

Steps:

  • To start with, copy cell C5 using CTRL+C.

using paste special to copy the same date in excel

  • Next, select the cells from C6 to C10 and right-click.
  • Here, select Paste Special and from there click on Paste Link.

  • Immediately, this will create copies of the date in cell C5 in the other cells.

Read More: How to Add Time in Excel Automatically


4. Copy Same Date from Clipboard

A Clipboard is a place where Excel keeps track of all the values that we copy. Although it requires some manual work, we can still copy the same date value using this feature in Excel. Let us see how to do that.

Steps:

  • Similarly, as before, copy cell C5 and click on the Clipboard icon in the top-left corner.

using clipboard to copy the same date in excel

  • Now, click on cell C6 and go to the Clipboard pane on the left.
  • Here, click on the drop-down and select Paste.

  • Finally, this will generate copies of the beginning date.

Read More: Making a List of Countries by Time Zone in Excel


5. Copying by DATE Function

The DATE function in Excel can take dynamic values as input and produce the respective date value. We can use this function to quickly copy the same date value to other cells. Let us see how to use this function.

Steps:

  • First of all, go to cell C5 and type in the following formula:
=DATE(2022,1,20)

using date function to copy the same date in excel

  • Finally, press the Enter key and copy this formula down using Fill Handle.

Read More: How to Calculate Due Date with Formula in Excel


6. Applying VBA Code

If you are familiar with VBA in Excel, then you can copy the same date to other cells with just a few clicks. Follow the steps below to do this.

Steps:

  • For this method, go to the Developer tab and select Visual Basic.

using vba code to copy the same date in excel

  • Now, select Insert in the VBA window and click on Module.

insert vba module to copy the same date in excel

  • Next, type in the formula below in the new window:
Public Sub CopySameDate()
Range("C6:C10").Value = Range("C5")
End Sub

  • Then, open the macro from the Developer tab by clicking on Macros.

  • Now, in the Macro window, select the CopySameDate macro and click Run.

  • As a result, the VBA code will create copies of the date in cell C5.

Read More:  How to Create World Time Zone Clock in Excel


How to Insert Dates In Excel Automatically

To insert dates automatically in Excel, we can again use the simple Fill Handle tool without typing any formula. Let us see the detailed steps.

Steps:

  • First, click on cell C5 and double-click on the + icon in the lower-right corner.

  • Consequently, this will automatically fill the cells below with increasing dates.

Read More: How to Use Excel Date Shortcut


How to Auto Fill Date In Excel Using Shortcut

In Excel, there are many shortcuts that can be applied from the keyboard to quickly achieve some result. Let us see how to auto-fill the date in Excel using a simple shortcut.

Steps:

  • For this, select all the cells from C5 to C10 and press Ctrl+D.

  • As a result, this shortcut will fill the dates in those cells automatically.


Download Practice Workbook

You can download the practice workbook from here.


Conclusion

I hope that you were able to apply the methods that I showed in this tutorial on how to copy the same date in Excel. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. If you have any queries, please let me know in the comments.


Related Articles


<< Go Back to Autofill Dates | Excel Autofill | 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.
Nazmul Hossain Shovon
Nazmul Hossain Shovon

Nazmul Hossain Shovon, a BUET graduate in Naval Architecture and Marine Engineering, embarked on his career with 8 months dedicated to the Exceldemy project's triumph. Transitioning into a Software Developer role, he specialized in web add-in development. At Exceldemy, he authored about 125 blog articles and solved many visitors’ problems, refining his writing skills and delving into Excel-related topics. With a primary passion for programming and software development, Shovon continually explores new horizons, fostering professional growth in his... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo