Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: If cell value = C then current time in another cell.

  1. #1
    New Member
    Join Date
    Mar 2016
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default If cell value = C then current time in another cell.

    Hello,

    I'm trying to create a checklist. There is a drop down menu on one end of the checklist that allows you to choose "C" to mark a particular item complete. I want the spread sheet to log the time each individual checklist item is complete. Currently I have in the cell that needs to show the time =IF(A5="C",NOW()," ") ...my intent here was if A5 is C then itll pop up with the current time in the cell showing that formula (G6.)...that formula works until you go down to the next checklist step and put a C in it's status block. It also puts the current time in its corresponding 'time complete' box but it also updates all other 'time complete' cells with the current time, instead of showing the time that they were completed. Is there a better way to approach this, either with formulas or Macros? Thanks.

    AJ

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    15,646
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    5 Thread(s)

    Default Re: If cell value = C then current time in another cell.

    You can't do it with formulas. You'll need a worksheet_change event macro.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    New Member
    Join Date
    Mar 2016
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If cell value = C then current time in another cell.

    can you be a little more specific? What's the code to get it to detect the change in A5 and put the current time in G6?

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    13,250
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    6 Thread(s)

    Default Re: If cell value = C then current time in another cell.

    Are you sure you want to enter "C" in A5 and have time in G6

    Would it not be A5 and G5
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  5. #5
    New Member
    Join Date
    Mar 2016
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If cell value = C then current time in another cell.

    I'm sure, merged cells and text in other cells and whatnot make it a little complicated. The Cell that needs to show the time is G6 when "C" is in A5, and when I change A10 to "C" I don't want G6 to go back and update with the current time again, I want it's time to stay...that's what I need. Looking for a little help.

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    13,250
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    6 Thread(s)

    Default Re: If cell value = C then current time in another cell.

    So we are only dealing with two ranges

    A5 and G6

    Nothing else

    So is A6 has a C entered nothing happens. Correct?
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  7. #7
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    13,250
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    6 Thread(s)

    Default Re: If cell value = C then current time in another cell.

    Try this:
    This is an auto sheet event script
    Your Workbook must be Macro enabled
    To install this code:
    Right-click on the sheet tab
    Select View Code from the pop-up context menu
    Paste the code in the VBA edit window

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 And Target.Row > 1 Then
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Target.Value = "C" Then Target.Offset(-1, 6).Value = Time
    End If
    End Sub
    Last edited by My Aswer Is This; Mar 21st, 2018 at 06:06 PM.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  8. #8
    New Member
    Join Date
    Mar 2016
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If cell value = C then current time in another cell.

    No A6 is merged with A5, A7, A8, and A9 to support text in adjacent cells. A10 is the next cell that will get a "C". and when there is a "C" in A10, G10 needs to show current time as well without affecting the time stamp in A6. If having the cells merged messes up the coding then I can fix that but the bottom line is A5 is going to get a "C" and the next cell to get a "C" will be A10. then A13, etc.

  9. #9
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    13,250
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    6 Thread(s)

    Default Re: If cell value = C then current time in another cell.

    Well if this is not going to be done for all cells in column A then we need some sort of trend here.

    Is it every other row or every 5th row or what.

    And is it always offset by one row as to where the time goes?

    Like A10 G9

    A50 G49

    A65 G64

    And none of the times will change after they are entered.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  10. #10
    New Member
    Join Date
    Mar 2016
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If cell value = C then current time in another cell.

    actually, I can see the logic in that code you just provided, and if I change the checklist up to match your logic then it works perfectly. So that's what I'm going to do. Thanks a lot for your help!

Some videos you may like

User Tag List

Tags for this Thread

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
  •