0

How to make a formula that if someone fill the cell it calculate the time now. For example if the employee come and the need to fill the absence, the formula will put the time they input for example 14:30.

I have already using formula =now(), but it is not working it update every time we put the absence.

Máté Juhász
  • 21,451
Dede Anugrah
  • 1
  • You don't want to use NOW() as it updates if you reopen the sheet etc., etc. it's mean to always give you the current time a calculation for that cell is done. I'm not exactly sure what you're trying to do either. Just make them write 14:30 in the field? What exactly do they fill in and what do you want to calculate? – Seth Sep 01 '16 at 08:50

1 Answers1

1

You have two options for achieving it:

  1. using iterative calculations:

    • go to file - options - formulas
      • check "enable iterative calculations"
      • set "maximum iterations" to 1
    • now you can use this formula:
      =IF(A1="","",IF(B1="",NOW(),B1))
      This will insert current date and time to B1 once anything is entered in A1, and won't update it later.

  2. using macros

    • press Alt+F11 to open VBA editor
    • double click the name of the worksheet where you want the data, this will open that sheet's code module
    • Enter this code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Target.Offset(0, 1) = Evaluate("now()")
    End If
End Sub

This code will enter now() in corresponding row of column B whenever a cell in column A is filled.

Máté Juhász
  • 21,451