If instead of checking for any specific word, you want the timestamp to appear when you put anything in the specified cell (that could be any number, text or date), then have the first IF function to check for a non-empty cell, like this: Note.
If you are curious to know how Excel stores dates and times, please check out part 1 of this tutorial - Excel date format.You can type a date in an Excel cell in a variety of ways, for example 1/1/2015, or 1-Jan-2015, or 1-Jan, or January 1, 2015.For the detailed instructions, please see How to insert calendar in Excel (Date Picker control).If you are using a 64-bit version of Excel 2016, Excel 2013, Microsoft's Date Picker control won't do.The most obvious visual indication that Excel has recognized the date you've entered is its right-alignment in a cell as opposed to left-aligned text values.
If Excel has failed to recognize your input as a date and you see it left-justified in a cell, try inserting a date in some other format close to your default short or long date formats.
Once "Yes" is in there, you want to have the current date and time automatically inserted in the same row in column C as a static unchangeable time stamp.
To do this, we are going to use the following nested IF formula with circular references in the second IF function: column, and C2 is the cell where you input the formula and where the time stamp will eventually appear.
Your users will be able to fill in dates in a mouse click and you will be 100% confident that all dates are entered in an appropriate format.
If you are using a 32-bit version of Excel, you can use Microsoft's Date Picker control.
In the above formula, the first IF function checks cell B2 for the word "Yes" (or any other text you supply to the formula), and if the specified text is there, it runs the second IF function, otherwise returns an empty string.