Newbie Question: Adding the Date to a cell that another cell is modified.

[kyzooper]

Hi New here
Im trying to track the orders someone else is ordering on my behalf.
The below is part of the spreadsheet:

ORDER RAISED IN XXXXX	DATE RAISED
YES	11/03/2023
YES	09/03/2023

I have a dropdown for "order raised in XXXXX" that YES or NO or Blank can be chosen. I want the date that YES is chosen to automatically populate in the "date raised" column.
I tried using =IF(E5="YES",TODAY(),""). The problem is every time i open the spreadsheet it updates the date to the current date. how do i lock the date to when YES was selected?
Hope someone can help
Thankyou

 

[Peter_SSs]

Welcome to the MrExcel board!

The easiest way is to use vba (macro) is that acceptable?

If so, what should happen if a cell is changed to YES and a date is inserted into Date Raised and then subsequently the YES cell is cleared or changed to NO?