EST DateTime To IST DateTime

Apr 3, 2009

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)

View 9 Replies


ADVERTISEMENT

Check A Datetime Is In GMT Or BST

Sep 22, 2009

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)

View 9 Replies View Related

How To Use DateTime (in Specified Cell) To Do A Select

Mar 31, 2014

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?

View 2 Replies View Related

Adding Business Hours To Datetime Value

Dec 18, 2009

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".

View 8 Replies View Related

Removing Seconds From Datetime Stamp?

Jul 4, 2014

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 Related

Saving A File With DateTime Stamp

Jun 9, 2008

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?

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.

View 9 Replies View Related

DateTime Stamp With Correct Number Format?

Nov 25, 2013

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.

View 2 Replies View Related

MS-Query Convert Datetime Column To Date

Jan 6, 2010

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,

View 9 Replies View Related

Determining If Datetime Falls Within Start And End Date Times

Sep 25, 2013

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.

View 4 Replies View Related

Determining If Datetime Falls Within Start And End Date Times

Sep 25, 2013

I have been using this statement.

if(and(c1>=a1:a144,c1

View 3 Replies View Related

Count Occurrences Of Time Stamp Between Two Times From DateTime Column

Jun 18, 2014

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.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved