Need today’s date to auto fill in a cell when data is entered into another cell

microsoft excel

I would like to have cells C5-C39 auto fill today's date whenever data is entered into cells B5-B39.

I have tried to get it to work using =TODAY() but don't how to structure the formula.

Best Answer

I'd say that the simplest way to do this would be with a user-defined function. That function will update when any cell it references change, so you won't have to tie it into any events.

A function for your problem would be:

Function Updating_Date(dependent_cell as Range) As Date
  Updating_Date = Date
End Function

After having put that code into a module in your workbook, you can simply input =Updating_Date(B5) into C5 and then copy it down. That should make the date in C5 update whenever the values in column C changes.

In order to make the date only appear if the cell in column B isn't blank, you could apply an if-formula in the cell in column C, as I touched on in the other question you asked. Simply enter the same conditional as I showed there (i.e. ISBLANK(B5)) and have the return-value be an empty string ("") if the condition is true, and the UDF (Updating_Date(B5)) if the condition is false.