Macro to Enter Date & Time in the Adjacent Cells of My Choice

[jdanniel]

Greetings. May I please request some assistance with a macro?

I want to create a macro that will automatically enter the date and time in adjacent cells.

I know the command for Date, and the command for Time.

Date = CTRL + ;
Time = CTRL + SHIFT + ;

The problem I’m having is this: The macro that I recorded (see below) always enters the Date in cell H2. And it always enters the Time in cell I2.
I want the macro to enter the date and time starting with the cell I put the cursor in prior to running the macro.
For example, if I put the cursor in cell H89, then that’s where I want the date entered, and then the time in I89.
How do I record or write the macro to do this? Or is there an easier way to do this?

Thank you very much. J. Danniel

Sub DateTime()
' DateTime Macro
' Keyboard Shortcut: Ctrl+d

Range("H2").Select
ActiveCell.FormulaR1C1 = "5/29/2023"
Range("I2").Select
ActiveCell.FormulaR1C1 = "7:56 PM"
Range("H2").Select
End Sub

 

[rollis13]

Have a try:

Option Explicit
Sub DateTime()
    ' DateTime Macro
    ' Keyboard Shortcut: Ctrl+d
    With ActiveCell
        If .Column = 8 Then
            .FormulaR1C1 = "5/29/2023"
            .Offset(0, 1).FormulaR1C1 = "7:56 PM"
        End If
    End With
End Sub

 

[jdanniel]

Thank you for replying, but your macro only puts in 5/29/2023 and 7:56pm in the cells, not the current date and time.

Also, when I drag the cells down to fill in cells below them, it changes the date and time incrementally.
For example:

5/29/2023 7:56PM
5/30/2023 8:56 PM
5/31/2023 9:56 PM

When I enter the date and time manually by using CTRL + ; and CTRL + SHIFT + ; and drag down the cells, it doesn't change the date and time, which is what I want.

I apologize for not mentioning that previously, and I apologize for not realizing the macro I posted had that specific date and time in it.

Can the macro be revised so that it enters the current date and time, and does not increase incrementally if I drag down the cells?

Thank you.

 

[p45cal]

try:

Sub DateTime()
' DateTime Macro
' Keyboard Shortcut: Ctrl+d
With ActiveCell
  .Value = Date
  .Offset(, 1).Value = Time
End With
End Sub

 

[rollis13]

I'm late .
So also: when pulling down you need to press Ctrl to make Excel not increase.

 

[p45cal]

Oh right:

Also, when I drag the cells down to fill in cells below them, it changes the date and time incrementally.

Select the range before you press Ctrl+d and use this variation:

Sub DateTime()
' DateTime Macro
' Keyboard Shortcut: Ctrl+d
With Selection.Columns(1)
  .Value = Date
  .Offset(, 1).Value = Time
End With
End Sub

It will put dates and times in the leftmost 2 columns of your (contiguous) selection.

 

[jdanniel]

Very good. Thank you for your help and patience.