Solved How can I have Excel enter a time in a field?

Microsoft Excel for mac 2011 - macintosh
May 7, 2011 at 09:13:46
Specs: Macintosh
Is it possible to have the time entered in a field upon the entrance of another field?

See More: How can I have Excel enter a time in a field?

Report •

✔ Best Answer
May 31, 2011 at 10:10:55
Right click the sheet tab for the sheet where you want this to happen.

Choose View Code.

Paste this code into the pane that opens.

Enter data in Column A.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Cells.Count = 1 Then
  If Target.Column = 1 Then
   Application.EnableEvents = False
    Cells(Target.Row, 2) = Now
   Application.EnableEvents = True
  End If
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
May 7, 2011 at 09:55:31
If by "field" you mean "cell", yes.

However if you use something like this, the time will update every time the sheet calculates:

=IF(B1<>"",NOW(),"")

If you want a "static" time placed in a cell, you'll need some fairly simple VBA code (i.e. a macro).

If using a macro is OK, then give us some details about what you are trying to do and we'll see what we can offer.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 14, 2011 at 11:08:53
That's fine. I set it up. The only problem is that when the next cell has data entered, the time that was previously entered changes to the current time. My set up is like this:

A1 -> B1

B1=time when A1 has data entered in it.

And so on.


Report •

#3
May 14, 2011 at 11:40:12
That's exactly what I meant when I mentioned needing VBA to place a "static" time in a cell.

BTW "And so on" doesn't mean much to us. It could mean D1=time when C1 has data entered in it or B2=time when A2 has data entered in it or a multitude of other things.

You need to be specific so that we can provide solutions that will work for you.

Assuming (a dangerous thing) that you meant Column B should have the time in it when Column A is populated, try this:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Cells.Count = 1 Then
  If Target.Column = 1 Then
   Application.EnableEvents = False
    Cells(Target.Row, 2) = Now
   Application.EnableEvents = True
  End If
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
May 19, 2011 at 16:14:18
http://cl.ly/250b0l1G21003V3a3c2M

That's how it is right now. The function in the Time column is:

=IF(B3>=9000,NOW(),"")

It does the job.

The ID numbers are inputted at a certain time (logging). Once a data entry is inputted, the time shows up.

Thank you for your code, though. I tried following a tutorial on including the snippet but I'm assuming I'm not doing it properly.

Thank you.


Report •

#5
May 19, 2011 at 16:37:03
I'm confused.

Using your formula, the time is going to change every time the sheets calculates.

I thought you didn't want that to happen.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
May 21, 2011 at 15:50:24
No no. You had the right idea. I don't want it to change. I was just hoping there was a script that allows the freezing of a cell in someway, which would freeze the cell after the time is entered.

Report •

#7
May 21, 2011 at 16:47:50
I already posted the code to do that in response #3.

Did you try it?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
May 23, 2011 at 20:17:22
Great. I'm trying to figure out how. I'll post a reply once I do.

Thank you.


Report •

#9
May 29, 2011 at 08:34:14
Derby,

I apologize for my inability to utilize your script, but I have no idea on how to begin. Where do I start? I've tried using "Run" but I wasn't using it properly, apparently.

Lend a hand. :)

Thank you.


Report •

#10
May 29, 2011 at 13:14:55
As I read back through this thread, I'm seeing contradicting information.

In Response # 2 you said:

"My set up is like this:
A1 -> B1
B1=time when A1 has data entered in it."

Then in Response # 4 you said:

"The function in the Time column is:
=IF(B3>=9000,NOW(),"")"

So which is it - is the data being entered in Column A so that Column B shows the time or is the data being entered in Column B with the time showing up someplace else?

Finally, the image you provided the link to shows 2 time columns, C & F.

Since the code can't "guess" what cells you are monitoring or "guess" where you want the time to appear, you need to be very specific with your requirements.

Exactly where are you entering data and exactly where do you want the time to appear?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
May 30, 2011 at 07:12:58
Sorry about that. They were only supposed to be temporary.

I moved the Staff ID and Time to columns A and B.

In response #2 I did say:
"My set up is like this:
A1 -> B1
B1=time when A1 has data entered in it."

And it Response #4, I meant to say:

"The function in the Time column (B) is:
=IF(A1>=9000,NOW(),"")"

This is after adjusting the Staff ID and Time to columns A and B.

Sorry for the confusion. Hope this clears things up.

Thank you.


Report •

#12
May 30, 2011 at 08:40:08
re: "Hope this clears things up"'

Almost!

Are you now saying that you want the time to appear in Column B as soon as data is entered into Column A but only if the data in Column A is >=9000?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#13
May 31, 2011 at 09:33:49
Well, >=9000 was only a substitute. I was trying to get it to be any number, but that substitutes for what I need.

And yes, I want the time to appear in Column B as soon as data is entered into Column A.

The initial problem I had was that every time the time was entered into a cell in Column A, and then entered again for another entry, the previous entries would update to the entry last updated.


Report •

#14
May 31, 2011 at 10:10:55
✔ Best Answer
Right click the sheet tab for the sheet where you want this to happen.

Choose View Code.

Paste this code into the pane that opens.

Enter data in Column A.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Cells.Count = 1 Then
  If Target.Column = 1 Then
   Application.EnableEvents = False
    Cells(Target.Row, 2) = Now
   Application.EnableEvents = True
  End If
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#15
May 31, 2011 at 16:53:36
Thank you! This worked wonderfully. I had to use other options because of different versions of Excel, but I managed.

Thank you for your time!


Report •

Ask Question