Is there a formula to remove seconds part from datetime stamp? 6/26/2014 6:14:25 AM, should be converted to 6/26/2014 6:14 AM I used ROUND((DATEVALUE(H2)+TIMEVALUE(H2))*1440,0)/1440 but it is providing #value! error
I have a macro which creates a new file and saves an extract of data to it. I would like to add a datetimestamp to the end of the filename while saving.
Is there anyway to do this using my existing macro with some changes?
I am trying to date and time stamp a certain cell. This cell is going to be compared to another cell in another sheet (also date and time) so that I can track deadlines being met.
Even with the number format, the cell is not stamped as a number. As a result I get #value errors when I take the difference between the stamped cell and the cell with the preset deadlines.
Been working on this issue for the last couple of days with no luck. I have a column of data that is a data and time stamp, for any date or time like below. This is an ever growing list as users continue to populate the data.
On a separate sheet I have a list of times by 15 minute increments starting at midnight until 11:59PM like below.
12:00:00 AM 12:15:00 AM 12:30:00 AM 12:45:00 AM
I'm trying to count how often a time stamp occurred between two 15 minute increments. ventually I'll be plotting this data on a graph to show where most occurrences happen.
I have only one column (A) with thousands of cell entries. Each Cell contains a date and time. (x/xx/xxxx 12:34:56)
For example, Cell A1 = 1/24/13 18:45:22
I want the new cells to only have the time, but have the seconds removed (and not simply hide the seconds). This is because I'll need to run Conditional Formatting to find every cell with a similar time - but I only need similar times within the same minute...it doesn't matter if the seconds are off.
The new cell should just be A1 = 18:45
The real reason I need this is because I need to find every entry that has the same time (with hour and minutes), which I figure I'd use conditional formatting for. My problem is that many entries have the same time but the seconds are off.
In this example, I'd want Conditional Formatting to highlight cell A2 and A3 because they have the same time of 18:45 (not including the seconds). I figured the best way to go about this would be to remove the date and remove the seconds, and run the search that way. However, any way to accomplish goal without deleting the date and seconds would be just as useful.
I trying to figure a formula to convert time on a phone call eg. 01:01:21 into total seconds (3661). Phonecalls will never be more than an hour long but the spreadsheet I will be supplied with (havn't got it yet!) will display them in the 00:00:00 format.
I am trying to convert a number of "hours" "minutes" and "seconds", to give me a result in seconds only, in order that I may then financially cost the amount of time spent on a task. (A time and motion costing exercise)
Example:
1119:48:06 Represents 1119hours:48minutes:36seconds spent on a task.
I can manually convert this to 4031286 seconds, but it just takes too much time !!!(sorry!)
The data is extracted from a database which is unable to split the hours,minutes,seconds into seperate fields, which prevents me from using three cells in excel.
How can I add a date stamp that will populate a field when text is entered into another cell but will not change every time I re-open the sheet. I have been using =if(B4="",NOW()) which changes each time though and as soon as text is entered it disappears. I want to capture the actual date that someone enters text into a cell and save that date in another cell???
i have a column "Received On" contains the datetime of a mail received , it is in BST, sometimes it should be in GMT depends on the date or year, i'm not sure. So what my query is, in another column, i have to check if the datetime in "Received On' is in BST, convert this datetime to GMT, otherwise if this time is in GMT, i don't need to add anything just copy that datetime alone.
eg: ie in ColumnA is the Datetime value
if ColumnA=BST then ColumnB=ColumnA+1/24 (ie convert BST to GMT) else if ColumnA=GMT then ColumnB=ColumnA (ie not need to change, becz already columnA is in GMT)
I have an array starting at, lets say "C25" -header line Row 24- with data entry and calculations to Colume "AW25". Each row represents a data record for a project, where different data types are used (Number & Text). A "termination" row is set as last row. New records are always enterd in row "25", but any row in the array is allowed to be deleted.
The number of rows can be extended or reduced, when entering/ deleting a new record (not all info are mandatory for calculations) - basic data is followed by lots of calculations for the record. This leads to the issue that the array is dynamic regards amount of row. To make it even more "fancy", the array can be sorted using data filter from row "25" to "last row -1".
I need to track when a record (any cell in the row) has been accessed/ modifed/ enterd last. The time stamp may only be altered when the particular record (row) is modified and NOT in case any of the cells is modified (NON-valotile) I have foreseen to enter that date in Colum "Bxyz" of the array. For a referrence I still have Colume "A" as "spare". Depending on time difference I've to mark the "date" cell with conditional formating (this is an easy task I know how to do...).
Does any one has an idea how to implement such routine (sub or Function).. Remeber, number of rows varies - up to now there are just 310 records, but will be much more in future. Not to forget ... some of the Colums have data avalidation set...
I have a system of checkboxes and now I need a time stamp when the check box was marked TRUE. They ar linked to cells so the solution can be either linked to a cell or to a checkbox. Obviously I can't use Today or Now functions because they are refreshed. I found many solutions for time stamps when a change is made in a cell, but none of them worked for me. I tried altering them with no succsess. I would be very happy if the solution would apply to the whole column, not one cell and checkbox, because I have A LOT of checkboxes.
The checkboxes are in column B and I want the time( date) stamps in column C.
I have been able to use a time stamp code to put a date and time in a cell... I then worked out how to use this to list dates and times in the same cell (instead of replacing the cells contents)... using this
I have a spreadsheet (obviously), I want to click a check box and have it date stamp with text, in the next available cell... if you know what i mean! I have attached a screenshot of what i have (the check boxes) and what i need (the writing in blue).
device a macro which should be able to provide a time stamp in Column B when the adjecent cell in Column C is updated.
i.e. If C1 is updated, B1 should get the time stamp. *& if the value in C1 is removed, then the time stamp in B1 should also be removed.
In addition to this, I would require the macro to unprotect the sheet before updating the timestamp and protecting it again after the time stamp has been enetered. further moving on to saving the sheet.
I tried to combine few updated macros to complete it bu have been unable to do so.
This time stamp macro is great but I would also like to record the person who said 'Y' in the same cell as the date stamp '12:00p.m. MWatson'. The persons name should come from the Username.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range([P13], Cells(Rows.Count, "p"))) Is Nothing Then 'change your range on this line" With ActiveSheet If Target.Value = "Y" Then Target.Offset(0, 1).Value = Now
I would like to take this VBA a step further. What I want to achieve is to date & time stamp a worksheet in cell A1 if any other cell within the sheet is altered. Below is the VBA I used today to enter the info if cell A1 is changed. Can anyone please help with my new target.
Just one more question: When a shared file is used is it possible to also enter the username of the person who's made the alteration.
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range ' Only look at single cell changes If Target.Count > 1 Then Exit Sub ' Set Target Range Set rng = Range("A1") ' Only look at that range If Intersect(Target, rng) Is Nothing Then Exit Sub ' Action if Condition(s) are met Target.Offset(, 1) = Format(Date, "d mmm yyyy") & " " & Format(Time, "h:mm") & " Hrs"