Current time for data entered in column

Whenever data is entered in a cell, current time should come auto i
another corresponding cell.  For example, time should automaticall
come in Y column for any data entered in any cell of the column A.  Fo
A10 time should come in Y10 and for A12 it should come in Y12 and s
on.  I need it for doing time and motion study.  Appreciate all help

--
Message posted from http://www.ExcelForum.com

0
4/12/2004 4:02:25 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
219 Views

Similar Articles

[PageSpeed] 37

Hi Mohitmahajan!

Based on JE McGimpsey (http://www.mcgimpsey.com/excel/timestamp.html)

Let's say that every time an entry is made in cells A2:A100, the 
corresponding cell in column Y should have the date and time entered. 
You could use this Worksheet_Change() macro - put it in the worksheet 
code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    With Target

       If .Count > 1 Then Exit Sub

       If Not Intersect(Range("A2:A100"), .Cells) Is Nothing Then

           Application.EnableEvents = False

           If IsEmpty(.Value) Then

              .Offset(0, 1).ClearContents

           Else

              With .Offset(0, 24)

                  .NumberFormat = "dd mmm yyyy hh:mm:ss"

                  .Value = Now

              End With

           End If

           Application.EnableEvents = True

       End If

    End With

End Sub

Note that this will clear the date-time when the target cell is 
cleared. Remove the "If IsEmpty(...)...End If" control structure if 
that's not desired.

You can, of course use any valid date/time format.


-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments) 
available free to good homes.
"mohitmahajan >" <<mohitmahajan.14l09z@excelforum-nospam.com> wrote in 
message news:mohitmahajan.14l09z@excelforum-nospam.com...
> Whenever data is entered in a cell, current time should come auto in
> another corresponding cell.  For example, time should automatically
> come in Y column for any data entered in any cell of the column A. 
> For
> A10 time should come in Y10 and for A12 it should come in Y12 and so
> on.  I need it for doing time and motion study.  Appreciate all 
> help.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
> 


0
njharker (1646)
4/12/2004 5:14:58 AM
Thanks Norman for the prompt help.  I tried using this but was los
since am not good with macros and VB - practically 0 in these.

Pls let me know how to use this/record this.  Let me know step by step
this will be big help

--
Message posted from http://www.ExcelForum.com

0
4/12/2004 6:30:45 AM
Hi
for getting started with event macros you may take a look at
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.cpearson.com/excel/events.htm

In this case try the following:
- open your workbook
- right-click on the worksheet tab name
- choose 'Code'
- paste the code in the appearing VBA editor window
- close the VBA editor
- save the workbook
- test it


-- 
Regards
Frank Kabel
Frankfurt, Germany


> Thanks Norman for the prompt help.  I tried using this but was lost
> since am not good with macros and VB - practically 0 in these.
> 
> Pls let me know how to use this/record this.  Let me know step by
> step, this will be big help.
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
0
frank.kabel (11126)
4/12/2004 7:43:31 AM
Just to add a little to one of the steps 
outlined nicely by Frank (just in case .. ) 

Before you proceed to:
>- paste the code in the appearing VBA editor window

do clear/delete the "defaults" appearing in the window, 
i.e. delete the lines below completely first:

Private Sub Worksheet_SelectionChange(ByVal Target As 
Excel.Range)

End Sub

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---

"Frank Kabel" wrote: 

>Hi
>for getting started with event macros you may take a look 
at
>http://www.mvps.org/dmcritchie/excel/getstarted.htm
>http://www.cpearson.com/excel/events.htm
>
>In this case try the following:
>- open your workbook
>- right-click on the worksheet tab name
>- choose 'Code'
>- paste the code in the appearing VBA editor window
>- close the VBA editor
>- save the workbook
>- test it
>
>
>-- 
>Regards
>Frank Kabel
>Frankfurt, Germany
0
demechanik (4694)
4/12/2004 8:06:58 AM
Thanks a lot for all your help, it works and has made my work easier.
Have a great day :O

--
Message posted from http://www.ExcelForum.com

0
4/13/2004 8:24:26 AM
You're welcome!
Thanks for feedback.
--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
"mohitmahajan >" <<mohitmahajan.14n72o@excelforum-nospam.com> wrote in
message news:mohitmahajan.14n72o@excelforum-nospam.com...
> Thanks a lot for all your help, it works and has made my work easier.
> Have a great day :O)
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
demechanik (4694)
4/13/2004 2:56:36 PM
Reply:

Similar Artilces:

counting data from a series
I have from a suvey in a 3 column table, where COL1=survey number, COL2=question number, and COL3=response, as such: COL1 COL2 COL3 a Q1 2 b Q1 3 c Q1 1 a Q2 3 b Q2 3 c Q2 1 a Q3 2 b Q3 3 c Q3 1 etc.. How can I calculate all the "3" responses for all Q2s? For example, based on the above sample table, I would expect the formula to return "2". =SUMPRODUCT(--(B1:B9="Q2"),--(C1:C9=3)) Hope this helps! In article ...

Deleting Data in Sample database Northwind traders
I would like to use the sample database Northwindtraders that comes with the software for my business but i need to delete all the data from all the connected templates especially the Inventory so i can enter my own data. Is this possible? or is thier others like it that don't come with the sample data included? I don't have months to learn how to use it Thanks Brad Dalton Create a brand new blank database. Then import all objects from the Northwind database into yours. On the import objects screen for tables make sure that you import "definition only." ...

New user
I need to backup my data and need to select the path...I am not even sure where to look for it...Help appreciated DJE <DJE@discussions.microsoft.com> wrote: > I need to backup my data and need to select the path...I am not even > sure where to look for it...Help appreciated http://www.howto-outlook.com/howto/backupandrestore.htm -- Brian Tillman ...

negative values causing column title problem
How do I get the Column titles x axis lables at he bottom of the chart field when have negative values It s friday and this is killing me. Select the axis Choose Format>Selected axis On the Patterns tab, for Tick Mark Labels, select Low TFrisch wrote: > How do I get the Column titles x axis lables at he bottom > of the chart field when have negative values > > It s friday and this is killing me. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html hey thanks - boy was thaqt annoying >-----Original Message----- >Select th...

Data Migration Framework Error
Hi All, Im having trouble importing email activites into MS CRM. When running the migration tool , Im getting the following error in the logs for every email I've set up as an activity. I've checked the guids for the owninguser and the objectid (contact), and they all exist. Im not creating any associated records in the cdf_ActivityParty table, as I only want to store the link for the "regarding" item so to speak. Thoughts anyone? Regards, Cameron 21/10/2004 - 8:43:18 AM -- [ERROR] Failed to migrate object: crmActivityUnknownError 21/10/2004 - 8:43:18 AM -- <a...

Print Current Page Only Macro
I am trying to come up with a macro with the ability to print th current page. It has become a big need within my company and will b a real time saver. Is there a way to search for the active cell an then print the current page it is in? In other words, by placin your cursor on the page you want to print, you could then run th print current page macro Hi SUB print_current() ActiveSheet.PrintOut end sub But why don't you sue the print icon in the toolbar. AFAIK this only prints the current selected sheet(s) HTH Frank vschlen wrote: > I am trying to come up with a macro with t...

data entry
I want to send a template to a customer which they fill in with ease. here what i want it to do....any help is appreciated. Trader Name Operating System Software Version Exchange1 Exchange2 options e.g. Joe Bloggs XP 2.6.1.18 check box check box 2000 2.6.1.19 check box check box NT 4 2.6.1.20 check box check box ME 2.6.1.6 check box check box when ...

using common data in multiple sapreadsheets
I would like to copy the results from one range of cells in one worksheet of one workbook, to another cell in another worksheet in another workbook, can this be done? Sure if both workbooks are open: workbooks("book1.xls").worksheets("sheet1").range("a1:b99").copy _ destination:=workbooks("book2.xls").worksheets("sheet2").range("c9") If you paste to one cell, then the copy|paste command will react just like you did it manually. It'll expand the destination range to be the same shape as the original range. Travis wrote: ...

Add a goal line to a column chart
I have several column charts which I need to add different goal lines. Any suggestions? The goal has to be represented as a line and not as a column. Thanks for your help! http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Brandy" <Brandy @discussions.microsoft.com> wrote in message news:EF99E151-13A4-461D-B9CA-DECE4E467904@microsoft.com... >I have several column charts which I need to add different goal lines. Any > sugg...

SELECT large amount of data in a worksheet
I have my mathematical model results in excel format every times I run my model. Huge amount of data --10,000 rows, with 10 columns. Now I like to copy certain portion of data into a new workbook for further analysis. And the data I wish to select is from F10:F10010 to P10:P10010. I can always select F10, scroll down and select P10010. This is a bit cumbersome and takes time. I cannot assign NAME to these data because every time I run my model, my range of data that I am interested in could differ -- meaning sometimes it could be ranging from H5:Q5 to H5000:Q5000. Is that a way for...

Re: Comparing Data Need to Include & Exclude
Well i tried it, i cant make it work. Did you try it to see if it worked??? and you got the Results i need as shown in list3 ?? What dioes E1 & E2 mean? Please explain. Try something like this: With your data in columns A and B E1: Missing (or any text other than the Col_B column title) E2: =COUNTIF($A$1:$A$27,B2)=0 (Notice the dollar sign ($) plaement in the formula AND that it refers to the FIRST DATA ITEM in Col_B) C1: ListB Select the Col_B data from B1 to the end of the list From the Excel main menu: <data><filter><advanced filter> Check: Copy to another...

Calculating time #3
I know this question has been asked before, but the answers that I have found do not seem to work for me for some reason. I am probably doing something wrong. What I need to do is calculate hours worked for the day. I have set up a spreadsheet to include the following: C3 is the time in D3 is the time out E3 is .5 hours (this is lunch) F3 is the total number hours worked for the day. The formula I have put in this cell is: =HOUR(D3-C3)-E3 [this is exactly how the formula is typed in] Now, if someone put their time in as 7:30 a.m. and their time out at 4:00 p.m. and they take the .5...

Onload JavaScript
Hello everybody, Is there a way to get the Business Unit the current user belongs to in the onLoad JScript on a form? Thank you, Hrvoje Solved, thanks. Hrvoje "Hrvoje" <hrvojev@recro-net.hr> wrote in message news:OoSQ2IdPKHA.1512@TK2MSFTNGP04.phx.gbl... > Hello everybody, > > Is there a way to get the Business Unit the current user belongs to in the > onLoad JScript on a form? > > Thank you, > Hrvoje > ...

Charts & Hidden Data
Hi, If I hide the row/columns containing the source data of a chart the respective series disappears from the chart. Is there any way to hide chart source data without affecting the chart? Many thanks, Jason In XL2003 (and before) with chart selected: Tools | Options |Chart and uncheck the box "Plot visible cells only" In XL 2007: Follow this path: click the chart; use Chart Tools|Design|Selected Data; look for button labelled "Hidden and Empty Cells"; complete dialog box as needed. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from ema...

Using an Object from a Current form for Criteria in a Query
I am using Access 2000. I have a Command Button on a form that is designed to preview or print that customer's invoice. I use a Pop-Up Dialog Box. The end user will supply 2 pieces of data in this Dialog Box. This data, together with the Customer ID from the current form. will provide the 3 necessary pieces of info to pass on to the query, and eventually the report, that creates the invoice. I suspect that the "Me." or "Me!" "command" or "action" must be used, but am not clear about how to use it. Perhaps someone can give me so...

Unhiding Protected Rows and Columns? is it possible?
I have a workbook with a number of worksheets that all interlink. I have protected a number of cells in one of the sheets. the problem arrises in that the unprotected cells cannot be hidden or indeed unhidden when the sheet is protected. Is there a way I can protect cells whilst still keeping the formatting ability to widen / hide rows etc. thanks -- guilf ------------------------------------------------------------------------ guilf's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25608 View this thread: http://www.excelforum.com/showthread.php?threadid=39...

Gridlines on Column Chart
Hello- I am trying to create a stacked column chart with gridlines that only appear for every 2 columns. How can I do this in Excel 2007? All I can find is how to insert a major gridline between every column. Thanks, Sean Hi, Format the X axis, on the Axis Options section set 'Interval between tickmarks' to 2. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "SeanF74" <SeanF74@discussions.microsoft.com> wrote in message news:E1BE2939-AB52-4FB1-8A0F-ACA9EFDB4CAD@microsoft.com... > Hello- > > I am trying to create a stacked ...

Control for displaying Data
Hi, I want to create an active control that will be having a dialog. On this dialog I want to show a table consisting of set of records. The data to this table will be coming from the web and will be refreshing again and again. Could you suggest, which control should I use to have the things done. I did not find Datagrid control suitable at this place. What are the other options available. FYI: Iam using VC++ 6.0 THANKS IN ADVANCE, Vijay You can use a list control (listview). But the popular one with a lot of features is: http://www.codeproject.com/miscctrl/gridctrl.asp --------------...

Time calculations for Scheduled Time vs. Actual Time Worked
I am trying to make a simple schedule worksheet for a manger to use to calculate the time he schedules an employee to clock in and out and what it will cost him in regards to his allocated budget and then to be able to compare it after the job is completed. BUT time calculations have thrown me for loop and I am stuck for hours on trying to calculate time fields. PLEASE DEAR GOD can someone help me. Below is a simple example of my worksheet. A2 (Time IN) = 1:00 PM A3 (Time OUT) = 5:00 PM A4 (Time Worked) = 4:00 (h:mm format) Formula A3...

Time line in excel graph
I need a vertical line in my graph to show that the graph information after the line is the future predicted outcomes. I've tried countless different options and cant seem to find how to do this! Hi, There are various ways of addressing this problem. See the following for examples of adding lines to charts. http://peltiertech.com/Excel/Charts/AddLine.html http://tushar-mehta.com/excel/charts/straight_lines/index.html http://www.andypope.info/charts/averageline.htm Cheers Andy Wilkesy50 wrote: > I need a vertical line in my graph to show that the graph information after > th...

accidentally entered #'s as %s
i have a hugh spread sheet. the numbers were accidentally entered as percentages. how can i get rid of the percentage formatting w/o the decimal point changing position. ie. i want -0.251% to read -.251. when i undo the percentage formatting it ='s -0.002514568. sorry:( i know it's a basic question, but i can figure this out for the life of me. thanks put 100 in a cell somewhere, and copy this cell, select the region o your values, and paste special, multiply, and reformat to numbe -- duan ----------------------------------------------------------------------- duane's...

Variable Column Lookup
I have three columns to the left column K each has a header with the year number. 2008, 2009 and 2010. Underneath each of the three columns I have numerical data. On the same header row I have a drop down list in K1. So when I pick 2009, I want the calculations in K2, K3 downward to use the corresponding data in column that has the matching year. 2008 2009 2010 2009 (drop down) 2 3 6 3*10 If I choose the drop down of 2008 then the formula should be 2*10 If I choose the drop down of 2010 then the formula should be 6*10 The actual formula is much more complic...

Stacked Column Charting
Has anyone tried the following? From the data set below, I would like to have the data to the right of the Hrs. avail data be a stacked bar for each day. Along with this the Hrs. avail data would be a bar next to the remaing row of data. I tried placing the Hrs. avail on its own axis but that did not seem to work. In short I want to show the number of hours availabe in a day and actual hours used by the verious other data. Regards, Hal Day Hrs. avail EM's PM's Cal's Projects Other Idle 1 14 4 2.5 2 4 0 1.5 2 14 5 1 1 3 0 4 3 14 0 2 2 6 0 4 4 7 2 0 0 3 0 2 5 14 2 0 0 10 0...

excel 2004 column sorting
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel I've read up a bit on others' sorting issues, but haven't come across exactly the same problem as mine. I used to be able to sort the info in one column and the corresponding info in the other columns would sort along with whichever column I chose at the first. Now, my corresponding columns don't sort along with my first sort, resulting in inaccurate info. Also, the workbook I am working with contains macros; the workbook contained macros when I first began using using it, i.e. when sorting was no prob....

insert number in column #2
I have a column with threedigits for telephone extensions. I want to insert a digit before the three throughout the column as the extension has been increased to 4 digits. Any easy way.../ Thanks. AFD One way: In a blank column (say column H), enter H1: =1 & A1 or, if you want them as numbers rather than text: H1: =1000+A1 copy down as far as necessary. Select the column and copy it. Select the original column and choose Edit/Paste Special/Values. In article <Om6vrJpEIHA.3548@TK2MSFTNGP06.phx.gbl>, "afdmello" <afdmello@hotmail.com> wrote: &...