I am trying to do something I thought was simple, I need to have the date populate a cell when a selection from a dropdown is made in another cell.

Details:


Column B cells are set as a dropdown list of employees.
Column C cells represent the date the Column B selection was made.
Date cannot change every time the spreadsheet is opened.

Attempts and Failures:
(with Column C cell Format set to Number/Custom: mm/dd/yyyy)
=IF(B2<>"",TODAY(),"") - WORKS but date changes every time sheet is opened

=IF(B2<>"",DATE(),"") - produces "Not trying to type a formula?" error

=IF(B2<>"",DATE(mm/dd/yyyy),"") - produces "Entered too few arguments" error

=IF(B2<>"",DATE(TODAY(),"") - produces 'Entered too few arguments" error

among others too numerous to count, only one works - and it changes every time I
open the sheet.

This is driving me mad, it should not be this hard. It is just a simple 'IF/THEN' scenario! And every search I've done to find the various formula 'date values' produces nothing usable.

PLEASE HELP!

Deleted. My solution did not work.

__________________
Alan עַם יִשְׂרָאֵל חַ Using O365 v2505

As start, are you sure you want to do this really. Using shortcut for current date (for me it is Ctrl+Shift+Semicolon, but it depends on your local settings) in cell in column C is so easy, there is no real reason to take much insecure approach.

Anyway, when you are sure about this, then formula in not a way for this. There is no way to prevent it to be recalculated at some appropriate moment. You even don't need to change anything - it will be enough the user double-click on cell (the editing of cell is activated), and then move to another one, and the formula is recalculated (and the date is replaced with probably new current date).

The only possible way I can think of, is to write a worksheet's Change event, which checks for values in columns B and C of active row, and when B<>"" and C="", then enters current date into cell of column C of currently active row. To make this even more foolproof, the script can check for active cell, and insert the date only when active cell is in column B or C. But the event fires whenever you edit any cell of worksheet, and at least checks for values of cells in columns B and C of active row - i.e. it spends some time for it and your workbook slows down as result.

See this link Automatically Enter Date When Data Entered in Excel (2 Ways)

Note that you need to set your options to Enable Iterative Calculation before it works

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia