=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.
I have been assigned the task of creating our new integrated time and leave sheet in Excel 2007, previously we had 2 sheets, one for times and one for leave.
The problem I have is that I need to protect the authorisaton columns therre are 2 one for AM and one for PM, so that only Managers can authorise leave by inputting a password and then initialing the leave.
What I then need to do a the end of each month is for the manager to be able to click a Button with a Macro which will then lock the whole worksheet so that employees cannot change their times after the manager has checked the sheet.
I can lock the authorisation columns and password protect but I have to enable protection on the sheet, when I do this I cannot then figure out a way of running a macro which will then protect the whole sheet and assign a password to it so it cannot be changed after being checked and signed off.
I have tried to create a macro (button) to remove the protection on the 2 columns and then reapply protection to the whole sheet with another button but to no avail.....
If anyone has any suggestions I would be eternaly greatful.
Passwords on the sheet for the different buttons are abc or cobra.
You can download my Spreadsheet here.
[url]
I recently posted a thread on locking one cell if another had a value in it, and vice versa for the other cell. I got this great response (thanks se1429!) and it works GREAT, but I failed to mention that the worksheet is password protected. It asks for a password when I enter a value in one of the cells. I just need help adjusting this code so I can put my password in the code and allow the worksheet to unlock and lock at will by using this password.
View 9 Replies View RelatedIs 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".
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 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 Replies View RelatedI'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 RelatedI have a file used by numerous facilties. I would like code to track any time a protected sheet has been unprotected / protected. I would also like the stamp to show the user data if that can be done.
View 2 Replies View Related