I am trying to set up my doc. so that when i double click with in columns (B&C)) the current time get inserted in a 24hr format. I am also trying to do the same thing but with a date formula (column A, also with the double click feature).
View 6 RepliesI have a workbook that requires refreshed source data each day. The workbook has all of the macros and formulas that analyze the data. I have the following code to import the worksheet with the raw data (onto a fresh worksheet in the calculation workbook), but I would like to create code that also adds the date and time to the imported data worksheet -- not the date/time the raw data was created; instead, when it was imported into my calculation workbook.
Below is my code for importing the raw data worksheet:
[Code] .....
I have been using the following code for some time ....
View 8 Replies View RelatedI need to be able to open a filenamed in the following format.
c:/workbook20060112.txt.
However the lsat part of the string is dyanmic and works with one business days lag (hence format sheet (date, "yyyymmdd") ) is not approriate. i also have the problem that that MS Excel does not have a holiday calendar (so this would not work over bank holidays/easte/exmas ect). The folder also contains archived files as below.
c:/workbook20060112.txt
c:/workbook20061130.txt
c:/workbook20061129.txt
I have been told about a filesearch object as well as a filesystem object. What situations would you use one over the other?
What I want it the most upto data file to pick up, but I dont want to do a loop where it look through all the files or something that counts down from today until it 'hits' the correct file.
Is there a way to put a time stamp with the users name into an adjacent cell when a cell is updated?
In case that didn’t make sense I will tell you what I need this for. We are creating new time cards that require both employee and manager approval on them. We have many remote employees and getting their signature can be a pain. If they could click a button on the time card that stamped their name and time that would be ideal. They would then send to their manager they would click to verify the time and it would stamp it as well.
I would like to set up an Excel spreadsheet for conducting a time and motion study in a workplace setting where someone follows an employee around to document how much time is spent on a variety of activities. Since the person would have a mobile tablet PC to walk around with and track the data, I would like to capture 3 fields of information:
1) Category of function being performed
2) Start date/time of function
3) Stop date/time of function
For #1, I would like to have a drop down box that appears in each row of Column A with a predefined list (i.e. Activity A, Activity B, Activity C, etc.). For #2 and #3, I would like to create a macro that "timestamps" the current date/time when a blank cell is "clicked".
=IF(D2<>"",IF(A2="",NOW(),A2),"")
Whenever I type into field D2 it populates current date and time in field A2 and down the A column as I write into current D columns. The next step I would like to do in which he was able to figure out so far is how to lock in the date and time after something has been typed into D2. If I make any changes to the D2 field it updates the date and time, which I dont want to happen.
What formula would I use if I wanted this:
If A1 is greater than 1 then B2 equals the time that I entered a # in cell A1
Is there anyway to create a timestamp based of a cell linked to a Listbox? For example if the linked cell is in cell C8, cell D12 would stamp the time.
View 1 Replies View RelatedI am using the following code to add in an entry to the sheet named "Upload Log". I am currently using the "Now" function to input date and time. problem is everytime the sheet changes the formula changes and alters the times. I am trying to create a log of each time this code runs. so I have a record of it. This code seems to work but rather than placing the formula in the cell as I have written here, I just want the code to place the values that formula would place, but not the actual formula. How can I change this code to do that?
Code: ...
I have A column that has values 0 or 1 (product active or not).
I want to add into B column date when change occured (from 1 to 0) but simple NOW or DATE is not working becouse program works so that it erases/resets all values and calculetes tham again wich resets mine date also.
How can i remamber value 0 and if it stays the same after reset that date in corresponding cell in B column stays the same and not resets.
Maybe some form of check is there date in B before chacking status in A?
I have a problem similar to Ozone64's posting of 4/23 that I am trying to generate automatic time/datestamps in a cell whenever a value is entered in an adjacent cell. I am currently using the function =IF(A3"",NOW()) but the problem is when data is entered in subsequent rows the previous timestamp is overridden with the new timestamp. Thus at the conclusion of data entry every row ends up with the same date/timestamp as the final entry.
I am attempting to enter Timestamp data from a movie in this format:
minute:second.millisecond
When I enter a frame timestamp of 18 minutes, 42 seconds, and 5 milliseconds, excel auto formats this data to 12:18:43AM.
This is not what I want. I have attempted multiple types of cell formats in an attempt to enter this data without auto formatting. One requirement for the format is the ability to subtract two of the times for a change in time measurement. I have tried every setting under the "Time" format list, as well as custom formats, which fail to subtract.
I've seen a lot of timestamp solutions but none of them fit my needs. Also my VBE skills are not good enough to create this. So here's the problem:
I've got 3 columns E,F en H. H contains a validation list with 3 options OK, NOK and OPEN.
If H is NOK I would like E and F to be blank. If H is OPEN I would like E to contain a start date wich doesn't update. If H is OK I would like F to contain an end date wich doesn't update.
I'm trying to make a directory whenever the macro runs that includes a timestamp. File names cannot include "/" or special characters, so I'm trying to change the format. However, even though NumberFormat changes it, excel still recognizes the date as having mm/dd/yyyy etc. so the directory cannot be made.
Here is a part of the code:
ActiveCell.FormulaR1C1 = "=NOW()"
Selection.NumberFormat = "yyyymmdd-hhmm"
DateTime = Selection.Value
strFileName = "Data Pull"
MkDir "C:..." & strFileName & DateTime
how I can modify the below code so that it populates the timestamp in the same row in Column E if Column A is populated with data...
Example:
Cell A1 is populated then populate Cell E1 with timestamp
if
Cell A4 is populated then populate Cell E4 with timestamp
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$A$2" Then
With Sheet1.Range("E2")
.Value = Time
End With
End If
End Sub
How to modify this code so that I select only one column triggers the time stamp update? For e.g. if i make any changes in column A, the date stamp is updated in the corresponding cell in column B. Basically, I am trying to narrow down to only one cell in the row, but it should work for any row in the sheet.
Refer to the below post: [URL] ....
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 1 Then Cells(Target.Row, "B") = Now()
End Sub
I have the below code that works fine. However I want to use a countif function to update Column I instead of manually having to type in the numbers each time they change. I know it would probably be a Worksheet_Calculate Sub but cannot seem to find one that works. The time stamp would be put into column H and the first row of data is row 3.
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("I3:I12"), .Cells) Is Nothing Then
Application.EnableEvents = False
[Code] .....
I received some time stamped data in this format: 06.08.06.000000000 PM.
convert to HH:MM:SS ?
I have a tab with about 10,000 records with IDs and timestamps (date and time). On another tab I have a list of IDs and start and end timestamps. There are duplicate IDs on both tabs. I want a formula for the 10,000 records tab that checks the ID and timestamp for each row against all the IDs and start/end time stamps of the other column and flags the rows where the ID and timestamp fall within the start/end timestamp on the other tab. It seems pretty simple, and it feels like the code should be something like this (but this code doesn't work):
=IF(AND(F2=List!C:C,B2>=List!F:F,B2<=List!G:G),"Y","N")
where F2 is the ID and B2 is the timestamp on the records tab. The "List" tab has the ID in column C and the start/end timestamps in columns F and G respectively.
I have a query that returns timestamps that looks likes these:
53425,114500,182834.
How can I display these numbers as AM/PM format?
I am creating a worksheet in excel 2003. Want to use this for attendance log.
As we input numercial data in column B, we would like in column I to create a formula that automatically inputs the actual time that info was entered in column B.
I've been looking all over for the most basic of VBA codes to insert a timestamp in a single cell (B1) when cell A1 changes due to formula result change. All the answers I've found are for manual updates of A1.
A1 has the simple formula: =SUM(F1:F10000)/3. I would like cell B1 to insert a new timestamp when the results of this formula in A1 change. On a weekl basis, I will paste-value data into the whole F column, which will change the resultes in A1.
If this can't be done, or is too complicated (I don't really write VBA, only copy and paste basic code), is it possible to have a timestamp inserted into B1 based on the paste-value event into the F column?
Excel 2010
I have a column of cells in my worksheet that look like this:
1/1/2007 10:53:31 AM
1/1/2008 8:35:46 AM
1/1/2008 8:35:54 AM
1/10/2008 1:39:26 PM
As you can see this is a timestamp in the format: m/d/yyy hh:mm:ss a/pm
The problem is that I’m trying to import this data into another program that accepts the same format without the SECONDS field: m/d/yyy hh:mm a/pm
Since I have thousands of rows of data it just isn’t feasible to go through and manually remove the seconds data from each cell.
Is there a macro or some other shortcut that can do this?
The ultimate goal would be to have the data above end up looking like this:
1/1/2007 10:53 AM
1/1/2008 8:35 AM
1/1/2008 8:35 AM
1/10/2008 1:39 PM
I have date/time data in the following form:
Mon Jan 12 07:26:26 EST 2009
I have tried multiple forms of LEFT, RIGHT, MID, etc. Date & Month are always three characters, but the date ("12" in this example) can vary between 1 or 2 characters.
Here is the formula I am currently using:
=SUMPRODUCT((Sheet1!B19:E15235="Contact Sheet Available/Disc Sent")*(MONTH(Sheet1!H19:H15235)=MONTH(TODAY())))
This counts all entries with the status "Contact Sheet Available/Disc Sent" and that have a timestamp within the current month.
I need to alter this to count the last week in September and the first 3 weeks in october (using this month as an example and will have to have to be able to run without changing dates next month).
I am just starting to learn about VBA and I have a range of data from collumns A through R. I would like to develop a macro that would place a time stamp in column S whenever the data is modified.
View 6 Replies View RelatedI want to automate the download process of these scan files we use. I can figure out how to do that if not for the two wildcards I'm about to throw out.
What I can't seem to figure out is how to use VBA to determine which files to import. I was thinking about possibly having VBA look for the time stamps on these files in the scan file directory, and then only import those ones which created in less than an hour from current time.
I have only a few days of VBA experience, so I don't have a clue where to start. If anyone could provide assistance/point me in the right direction.
By the way, the files that I am importing will be a comma delimited file. However, for some reason, the files are all extensionless (without .xxx)
for my kind of business we start off with 1 worksheet that has the original plan. We name this sheet "R(0)"
Every time the plan is revised (modified) it is only done by first creating a new worksheet then saving the revisions there. When we create a new worksheet it is automatically named "R(1)" , as in Revision 1.
My request is the following;
i have placed cell number "D86" to reflect the unique date that the sheet is created, so that every time a revision is done when creating a new sheet, the idea is to have this cell automatically update, inorder to reflect the date & time the revision was created. So at the end of the week when you have 5 revisions (5 worksheets), each worksheet will reflect the unique date & time of the revision.
I seem to be spending an increasing amount of time on here these days! I hope this one is quite simple - I want to add a time stamp of when a cell is updated, but I dont know how to do this. I presume that an adjacent cell can have =now(), but need to know how it to run this when a particular cell has been changed.
View 9 Replies View Related