Nested IF function to stop TODAY() updating to TODAY's date

[zipke]

Hi all,

Question related to the TODAY() formula. I have a very basic table with 2 columns: TASK and DATE

The idea is: when you enter a task in the TASK column, it automatically enters the date on which the task was added to the list.
I manage this with the formula:

Please Login or Register  to view this content.

Capture.PNG


Problem: when you open the Excel, it recalculates everything and automatically changes every old date into TODAY()'s date. It basically runs the formula again.

I thought about solving it with a nested IF function, but I don't get my head around it, and it feels like I am building a loop (I know the parentheses are not ok, just trying to explain the formula.

Please Login or Register  to view this content.

In words:
If A2 is not blank
>>>>THEN
>>>>IF B2 is blank
>>>>>>>THEN add today's date,
>>>>>>>otherwise (IF B2 is not blank) don't do anything
otherwise (if A2 is blank)
THEN leave B2 empty


A would need a solution without VBA if possible ...

Thanks in advance!

[way2020]

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Paul]

You would need to use VBA for this task, likely the Worksheet_Change event to see if a cell in column A has changed, and if so update the cell in column B of that row to reflect the date A2 was entered. (You may also want to have code to update the date if A2 was already filled and has now changed (if the same, don't update date). And if deleted, remove the date.)

[zipke]

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

The file is attached with the Before and After tab.

[Paul]

Using a combination of the Worksheet_Change, Worksheet_SelectionChange and a global variable (in a module), this may work for you.
The following code goes into the worksheet code module for the sheet with your tasks/dates (right-click tab, select View Code, paste this code into the VB editor)

Please Login or Register  to view this content.

This line of code sets a global variable, and goes in a module. In the VB Editor, click Insert > Module, then paste this line. It basically captures the cell value of any cell you click on while on the sheet with the 'SelectionChange' event code. This is so it can compare the previous value with the new value. (If the cell was "Test Task 1", and you re-type "Test Task 1", you probably don't want the date to change, hence this check. If it was "Test Task 1" and now it's "Test Task 2", a new date will appear in column B.)

Please Login or Register  to view this content.