"IF" Formula in Excel

B

bbott

Well-known Member

Joined

Feb 5, 2010

Messages

2,350

• Aug 15, 2014

• #2

Try this in H2:

=IF(G2="Completed",TODAY(),"")

But be aware that TODAY() will always be the current date. You would need to use VBA if you want to keep the date (i.e. today it will display 8/15/14, tomorrow it will display 8/16/14, and so on).

 

Last edited: Aug 15, 2014

Upvote 0

D

dgal79

New Member

Joined

Dec 3, 2010

Messages

6

• Aug 15, 2014

• #3

Thanks bbott but that doesnt work. The response is a weird number...41866 (not sure where thats pulling from). And correct...what i basically want is whenever a task is set to "Completed" I want the date it was set at.

 

Upvote 0

D

DeezNuts

Board Regular

Joined

Aug 12, 2014

Messages

177

• Aug 15, 2014

• #4

dgal you need to change the cell format to dates in the ribbon

 

Upvote 0

D

dgal79

New Member

Joined

Dec 3, 2010

Messages

6

• Aug 15, 2014

• #5

THANK YOU THANK YOU THANK YOU!!!! It worked!!! Much appreciated!

 

Upvote 0

B

bbott

Well-known Member

Joined

Feb 5, 2010

Messages

2,350

• Aug 15, 2014

• #6

dgal79 said:

what i basically want is whenever a task is set to "Completed" I want the date it was set at.

Click to expand...

The TODAY() function will always change. Regardless of when the task becomes completed, the formula will always change to the current date. I don't think that formula will do what you want it to do.....

 

Upvote 0

D

dgal79

New Member

Joined

Dec 3, 2010

Messages

6

• Aug 15, 2014

• #7

Oh so tomorrow the whole column that has "Completed" in it will change to the current date?

Is there anyway to do what I am asking?

 

Upvote 0

B

bbott

Well-known Member

Joined

Feb 5, 2010

Messages

2,350

• Aug 15, 2014

• #8

dgal79 said:

Oh so tomorrow the whole column that has "Completed" in it will change to the current date?

Is there anyway to do what I am asking?

Click to expand...

Correct.

You can't do it with just a formula, but there are VBA solutions if you are comfortable doing that.

 

Upvote 0

D

dgal79

New Member

Joined

Dec 3, 2010

Messages

6

• Aug 15, 2014

• #9

I am not too familiar with the coding for VBA's but if its just an easy code for this I am willing to give it a shot. Can you help?

 

Upvote 0

B

bbott

Well-known Member

Joined

Feb 5, 2010

Messages

2,350

• Aug 15, 2014

• #10

dgal79 said:

I am not too familiar with the coding for VBA's but if its just an easy code for this I am willing to give it a shot. Can you help?

Click to expand...

Sure. This code will update column H when the cell in the same row of column G is changed to completed. This is set to only work with the range "G2:G1000", which you can change if need be. To implement:

1. Copy the code below.
2. Right click on the sheet tab that you want the code to run on.
3. Click 'View Code'
4. Paste the code into the VBA window.
5. Close the VBA editor.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim x As Range
    Dim y As Range
    
    Set x = Range("G2:G1000")
    Set y = Intersect(Target, x)
    
    On Error GoTo 500
    If y.Value = "Completed" Then
        y.Offset(0, 1) = Date
    End If
    
500
End Sub

 

Upvote 0