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.


Download Practice Workbook

You can download the practice workbook from here.


6 Simple Methods to Copy Same Date in Excel

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.

Read More: Formula to Copy and Paste Values in Excel (5 Examples)


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 Copy a Cell in Excel Using Formula(7 Methods)


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: VBA Paste Special to Copy Values and Formats in Excel (9 Examples)


Similar Readings


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: How to Paste From Clipboard to Excel Using VBA


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: Copy and Paste is Not Working in Excel (9 Reasons & Solutions)


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 Copy and Paste in Excel Using VBA (7 Methods)


Similar Readings


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 Copy and Paste in Excel without Formulas (7 Easy Tricks)


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.

Read More: [Fixed]: Right Click Copy and Paste Not Working in Excel (11 Solutions)


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. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.


Related Articles

Nazmul Hossain Shovon
Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo