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

AKENNA431

New Member
Joined
Mar 3, 2016
Messages
19
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
You can't do it with formulas. You'll need a worksheet_change event macro.
 
Upvote 0
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?
 
Upvote 0
Are you sure you want to enter "C" in A5 and have time in G6

Would it not be A5 and G5
 
Upvote 0
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.
 
Upvote 0
So we are only dealing with two ranges

A5 and G6

Nothing else

So is A6 has a C entered nothing happens. Correct?
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top