How to auto add/enter current date/time in a cell with double clicking in Excel?
Frequently entering the current date or date and time in Excel cells can be a repetitive task, especially when compiling logs, tracking progress, or managing schedules. Manually typing the date each time is not only time-consuming, but also prone to errors. This article introduces an efficient approach to address this challenge: using a simple double click to automatically insert the current date or date/time into any cell within a specified range in your worksheet. By following the solutions below, you can streamline your workflow and ensure consistency in data entry.
Double click to auto add/enter current date or date time with VBA code
Double click to auto add/enter current date or date time with VBA code
If you want to insert the current date or date time into a cell simply by double clicking it, you can easily accomplish this with a short VBA code. This method is well-suited for users working on forms, attendance sheets, or any scenario where dates need to be logged quickly and accurately. The VBA solution provides flexibility to target a specific cell range, and updates are instant upon double-clicking.
1. In your worksheet, right-click the tab at the bottom for the sheet where you want this feature. Then choose View Code from the menu that appears. This opens the Visual Basic Editor for that worksheet only; make sure you are editing the correct sheet because the code will apply only to it.
2. In the Microsoft Visual Basic for Applications window, copy and paste the VBA code below into the sheet’s Code window.
VBA code: Double click to add current date into a cell
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
Cancel = True
Target.Formula = Date
End If
End SubNotes:
Practical tip: Double clicking outside the specified range will not trigger the date insertion; ensure your target cells are included within the designated range.
Error reminder: If you find the code is not running as expected, confirm you have entered the code in the correct worksheet (not in a standard module), and macros are enabled in your Excel environment. If security settings block macro activity, you may need to adjust them via File > Options > Trust Center > Trust Center Settings.
3. After the code is entered, press Alt + Q together to close the Microsoft Visual Basic for Applications window and return to your worksheet. This ensures any changes in the editor are saved.
Now, whenever you double click any cell inside the specified range (for example, A1:B10), Excel will automatically populate that cell with the current date or date and time depending on your code adjustment. This simple automation saves time and helps maintain consistent record-keeping.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Related articles:
- How to double click a cell and add1 to that cell value in Excel?
- How to add check mark in a cell with double clicking in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in