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".
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 Replies View RelatedI 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.
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
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.
