IF formula for adding date changing previous dates to current date

Copper Contributor

Forgive me, as this is my first time using this forum. 

I have been using a formula to add the current date to a column of cells when text is added to a specified cell. See example formula below:


=IF(B1 ="", "", TODAY())

=IF(B2 ="", "", TODAY())



The formula does add the current date to the specified cell (the If function is in the A column, so A1 and A2 would be affected respectively per the function above, etc) . For example, if I were to add text to B1 on 09/20/2022, A1 would correctly display the date 09/20/2022. 

However, if on 09/21/2022 I add text to B2, A2 would display 09/21/2022 correctly, but A1 would also be changed from 9/20/2022 to 09/21/2022. This happens with all prior dates using this function. 

Is there a way to rewrite the function so that when text is input into B2 (or B3, B4, etc), it will only change the date of A2 (or A3, A4, etc) and NOT the date of any prior A cells using the function?


I currently am using Windows 11 and the most current desktop version of Excel. 


Any clarification can be provided and any help would be greatly appreciated. 




1 Reply


Maybe with the Worksheet_Change event. In the attached file you can make an entry in any cell in column B and today's date is added in the adjacent cell in column A. The date in column A is only overriden if a new entry in the adjacent cell in column B is made.