Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: "IF" Formula in Excel

  1. #1
    New Member
    Join Date
    Dec 2010
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default "IF" Formula in Excel

    Hello,
    I need some help please!
    I am trying to create a formula in Excel 2013 that basically states, if in G2 is the text "Completed" then insert today's date in H2

    This is what i have so far but it's not working!
    =IF("Completed"=(J17)=TRUE,"=Today()"=(K17))

    I would really appreciate the help!

  2. #2
    Board Regular
    Join Date
    Feb 2010
    Location
    Wisconsin
    Posts
    2,350
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "IF" Formula in Excel

    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 by bbott; Aug 15th, 2014 at 04:42 PM.
    Using Excel 2013

    "Short answer yes with an if, long answer no with a but."
    -Reverend Lovejoy, The Simpsons

  3. #3
    New Member
    Join Date
    Dec 2010
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "IF" Formula in Excel

    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.

  4. #4
    Board Regular
    Join Date
    Aug 2014
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "IF" Formula in Excel

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

  5. #5
    New Member
    Join Date
    Dec 2010
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "IF" Formula in Excel

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

  6. #6
    Board Regular
    Join Date
    Feb 2010
    Location
    Wisconsin
    Posts
    2,350
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "IF" Formula in Excel

    Quote Originally Posted by dgal79 View Post
    what i basically want is whenever a task is set to "Completed" I want the date it was set at.
    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.....
    Using Excel 2013

    "Short answer yes with an if, long answer no with a but."
    -Reverend Lovejoy, The Simpsons

  7. #7
    New Member
    Join Date
    Dec 2010
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "IF" Formula in Excel

    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?

  8. #8
    Board Regular
    Join Date
    Feb 2010
    Location
    Wisconsin
    Posts
    2,350
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "IF" Formula in Excel

    Quote Originally Posted by dgal79 View Post
    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?
    Correct.

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

    "Short answer yes with an if, long answer no with a but."
    -Reverend Lovejoy, The Simpsons

  9. #9
    New Member
    Join Date
    Dec 2010
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "IF" Formula in Excel

    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?

  10. #10
    Board Regular
    Join Date
    Feb 2010
    Location
    Wisconsin
    Posts
    2,350
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "IF" Formula in Excel

    Quote Originally Posted by dgal79 View Post
    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?
    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
    Using Excel 2013

    "Short answer yes with an if, long answer no with a but."
    -Reverend Lovejoy, The Simpsons

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •