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
View 2 RepliesI 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?
My relevant part of the macro looks like this.
Workbooks.Add
ActiveWorkbook.SaveAs Filename:= _
"W:PJ_Report.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
This saves the file as PJ_Report.xls
I would like this to be
PJ_Report_20080609110403.xls
or even
PJ_Report_20080609 will do.
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.
I am using the following code to stamp the cell:
VB:
Range("F26").NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@".Value = Now()
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.
13/04/2014 20:00
28/04/2014 17:30
09/04/2014 13:30
09/04/2014 14:00
03/05/2014 7:30
03/04/2014 11:10
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 need a macro, if possible, to remove the seconds in a date/time string.
I can format the cells to remove the seconds, but the actual value still contains the seconds. For example:
The cell shows 9/24/2013 19:21 but the formula bar shows 9/24/2013 7:21:17 PM.
I need to remove the seconds so I can us the time in an averageifs formula as a criteria.
I would like a macro because I want to do other manipulations of the spreadsheet with the idea of importing the data at some point into a database.
If I can automate it, or at least decrease tha amount of time I use to get the data in the correct format.
I want to change 1 minute 24 seconds and 5 tenths into a seconds number. So basically it would come out as 84.50 seconds.
View 4 Replies View RelatedI 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.
Example:
1/22/13 12:23:11
1/24/13 18:45:22
1/24/13 18:45:36
1/26/13 22:33:41
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.
How can I Convert Decimal Minutes & Seconds To Total Seconds?
Here is the sample
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.
View 5 Replies View RelatedIs there an easy way of converting seconds from using a time into hours, minutes, seconds
View 9 Replies View RelatedHow would I go about converting - for example:
0:22 (formated general) to a number = 22
or
1:30 (formated general) to a number = 90
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.
I have a spreadsheet that imports data relating to phone call durations. The information imports as a total number of seconds taken.
What I need to do is to change that number of seconds into hours, minutes and seconds.
Changing the format of the cells doesn't work.
I've tried everything I can think of, and run out of ideas.
example:
23 needs changing to 00:00:23
96 needs changing to 00:01:36
268 to 00:04:28
9374 to 02:35:14
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???
View 5 Replies View RelatedI have been trying for the last two weeks to convert a list of seconds into minutes and seconds, but just can not do it?
Below is an example of my raw data, that I need to convert into minutes and seconds:
120000
120000
120000
2880000
480000
[code].....
We have server in Troy(USA).So we are in the situation to convert EST DateTime to IST DateTime in EXCEL.
If you have any formula or Idea share with me.
Eg; EST : 04/03/09 09:31:53 (I/P)
IST : 04/03/09 07:01:53 (O/P)
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'm trying to do a select function in Google Spreadsheet.
=QUERY('Page1'!A:C;"select A,B,C where A >= datetime "&(F2) ; 1)
F2 contains a DateTime value : 28/03/2014 00:00:00.
I'm receiving this error:
Error: Unable to parse the query string parameter to QUERY Function 2: PARSE_ERROREncountered "A> = datetime 41726" at line 1, column 20.
Why spreadsheet returns an Integer value? How do I convert this int value to a DateTime?
I want to write a macro to add business hours to a datetime value, excluding weekends, non-working hours and holidays.
e.g., I have a value (datetime) of "18/12/2009 11:30:00 AM", I want to add 10 hours to this, so as to get the result as "19/12/2009 12:30:00 PM".
I am working on MS-Query in Excel.
Selecting few datetime columns by using ms query but it returns that columns data in wrong format.
And CONVERT function is also not working in ms-query.
Below is the MS-Query:
SELECT `Consolidated$`.Payroll_ID, `Consolidated$`.Full_Name, `Consolidated$`.`Current Designation`, `Consolidated$`.`Date of Joining`, `Consolidated$`.Department,
I have been using this statement
if(and(c1>=a1:a144,c1<=b1:b144),"yes","no"))
And it just works for the first 2 values c1, c2 and doesn't fit for the others.
The case is i have more than one event at the same video and i need to confirm that no event was taken unless it is between start and end.
Here are some samples:
Start dtime End Dtime Event Dtime
16/09/2013 22:13:34 16/09/2013 22:14:18 16/09/2013 22:13:38
16/09/2013 22:15:57 16/09/2013 22:24:30 16/09/2013 22:16:02
16/09/2013 22:24:30 16/09/2013 22:33:49 16/09/2013 22:17:32
16/09/2013 22:33:53 16/09/2013 22:35:05 16/09/2013 22:19:02
16/09/2013 22:35:05 16/09/2013 22:39:57 16/09/2013 22:20:02
So as you can see there are more than one event between one start and end dtimes.
I have been using this statement.
if(and(c1>=a1:a144,c1
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...
How can I make a hot key that puts todays date and my name on it so I don't have to type it over and over again?
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
View 7 Replies View RelatedI 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).
View 7 Replies View Related 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
[Code] ........
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"
End Sub