Forum Discussion

McKenzieb520's avatar
McKenzieb520
Copper Contributor
Oct 17, 2022

Automatic Date entry

I hope someone can help and understand what i am asking.

 

I have column B:B where data is inputted and would like column C:C to automatically input the date the data was inputted into column B:B. I dont know if that makes sense i hope someone can help.

Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Oct 17, 2022

    McKenzieb520 

    Even though Mr. Hans Vogelaar was too quick for me :))),

    I can recommend the NOW () function in addition to and as an alternative to Mr.Hans Vogelaar suggestion.

     

    Example: =IF(A1="";"";IF(B1="";NOW();B1)).

    Requirement what Mr. Hans Vogelaar  described as a requirement (under Extras - Options - Calculation - Iteration - set the maximum number of iterations to 1).

     

    Attached is an older example/file form Internet with VBA code, similar.

     

    NikolinoDE

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 17, 2022

    McKenzieb520 

    Option 1: using formulas.

    Before entering the formula, select File > Options > Formulas.

    Tick the check box 'Enable iterative calculation' and set 'Maximum Iterations' to 1. Then click OK.

    Enter the following formula in C2 and format C2 as a date, then fill down as far as you want.

     

    =IF(B2<>"",IF(C2<>"",C2,TODAY()),"")

     

    Option 2: using VBA code.

    This does not require enabling iterative calculation, but users will have to allow macros.

    Format column C as a date.

    Right-click the sheet tab and select 'View Code' from the context menu.

    Copy the code listed below into the worksheet module.

    Switch back to Excel and save the workbook as a macro-enabled workbook (*.xlsm).

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Not Intersect(Range("B2:B" & Rows.Count), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rng In Intersect(Range("B2:B" & Rows.Count), Target)
            If rng.Value = "" Then
                rng.Offset(0, 1).ClearContents
            Else
                rng.Offset(0, 1).Value = Date
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

     

Resources