Populate current date in cell based on content in another cell

Question

Populate current date in cell based on content in another cell

Lori Steinberg 0

I have a spreadsheet with a column cell that I would like to auto populate the current date if another column cell text is "Complete"

Barry Schwarz 6,191 Reputation points

2026-04-17T19:03:40.48+00:00

Do you want to update the target cell when you enter a specific value in the test cell? Or are the text values already in the test cells and you want to scan them for the specific value and then update the target cells?

1 answer

Your answer

Barry Schwarz 6,191 Reputation points

2026-04-17T19:03:40.48+00:00

Do you want to update the target cell when you enter a specific value in the test cell? Or are the text values already in the test cells and you want to scan them for the specific value and then update the target cells?

Answer 1

Use an IF formula that checks the status cell and returns today’s date when it equals "Complete".

Assume:

Column A has the status (e.g., A2 = "Complete").
Column B should show the date.

In B2 enter:

=IF(A2="Complete",TODAY(),"")

Then copy/fill this formula down column B.

Notes:

TODAY() returns the current date and updates whenever the workbook recalculates or opens, so the date will change every day, not stay as the original completion date.
Format column B as a date if needed: Home → Number Format → Short Date or Long Date.

If a static (non‑changing) completion date is required, a worksheet formula alone cannot do this; a macro or manual entry (e.g., Ctrl+; to insert today’s date) is needed.

References: