Using IF Then statement to insert timestamp date

[geechung7089]

Hi Excel Forum,

I created a drop down menu in one cell with two choices using data validation. One is "In Progress" and "Completed". What I am trying to do is that with a IF THEN statement, if with the drop-down menu "Completed" is selected then in the neighboring cell it will timestamp with today's date that is static which will not change when you open the spreadsheet. Could you please explain what IF THEN statement that can do this?

 

[etaf]

have a read here , explains time stamps

How to insert timestamps in excel sheet using formulas

Often when you use excel to track a particular item (like expenses, exercise schedules, investments) you usually enter the current date (and time). This is nothing but timestamping. Once the item is time stamped, it is much more easier to analyze it. Here is an excel formula trick to generate...

chandoo.org

and

https://howtoexcelatexcel.com/excel-tips-tricks/create-a-timestamp-in-excel-with-formulas/

Instead of NOW() for the time , you can use TODAY() for the date

Then in the IF
use
IF ( cell with dropdown = "completed", IF( cell next to dropdown ="", today(), cell next to dropdown ),"")
by default iterations in formula computation is turned off. , you need to turn on

 

[geechung7089]

Hi Etaf,

I tried entering the formula as you stated but I am still getting an error message. Could you use the cells C5 and D5 in your illustration?
I also turned on iteration and set it to 1.

Thanks

 

[etaf]

=IF(C5="completed",IF(D5="",TODAY(),D5),"")

Book1
	A	B	C	D	E
3
4
5			completed	8/1/20
6
7
8			IF ( cell with dropdown = "completed", IF( cell next to dropdown ="", today(), cell next to dropdown ),"")
9
Sheet1

Cell Formulas
Range	Formula
D5	D5	=IF(C5="completed",IF(D5="",TODAY(),D5),"")

 

[geechung7089]

Hi Etaf,

The formula works, however, after I save and exit from the file and go back to it later, I have to recheck the enable iterative calculation check box every time, I open the spreadsheet. How can I make sure that everytime I open the spreadsheet, this function is enabled?

Thanks

 

[Peter_SSs]

Would you consider using vba to do the date stamp using Worksheet_Change event code like this? To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

This particular code monitors column C for "Completed" or not & puts the date in column D but could be set to any range or single cell if required.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim changed As Range, cell As Range
  
  Set changed = Intersect(Target, Columns("C"))
  If Not changed Is Nothing Then
    Application.EnableEvents = False
    For Each cell In changed
      If LCase(cell.Value) = "completed" Then
        cell.Offset(, 1).Value = Date
      Else
        cell.Offset(, 1).ClearContents
      End If
    Next cell
    Application.EnableEvents = True
  End If
End Sub

 

[etaf]

I have to recheck the enable iterative calculation check box every time, I open the spreadsheet.

My old version 2013 on windows and the current version i use 365 on OSX MacBook - the iterations setting stay set for the workbook on both. Not sure why its changing on your system
What version of excel do you have running on what software , Windows 10 ?????
Not sure what the fix would be , except to use Peter_SSs suggestion VBA , BUT the iterations should stay set.