EST/DST Time

hi
i would like to make a cell that shows the time in EST 
throughout the year. Note: i have normal time set in 
my "Time And Date Property", which means it changes time 
with daylight saving. Please  help me find a soltion so 
that i get EST time in that excel worksheet all thoughout 
the year.
thank you.
0
4/9/2004 9:54:32 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
760 Views

Similar Articles

[PageSpeed] 16

What is your normal time, pacific?
If so use

=TEXT(NOW()+3/24,"hh:mm")

mountain

=TEXT(NOW()+2/24,"hh:mm")

central

=TEXT(NOW()+1/24,"hh:mm")

use n/24 depending on where you are

-- 

Regards,

Peo Sjoblom

"Himu" <cricket_life@hotmail.com> wrote in message
news:1aa6301c41e7d$3d833210$a501280a@phx.gbl...
> hi
> i would like to make a cell that shows the time in EST
> throughout the year. Note: i have normal time set in
> my "Time And Date Property", which means it changes time
> with daylight saving. Please  help me find a soltion so
> that i get EST time in that excel worksheet all thoughout
> the year.
> thank you.


0
terre081 (3244)
4/9/2004 10:06:32 PM
Hi Himu!

I think I'm reading this differently from Peo.

What you are saying is that you are in the EST Zone and have system 
settings that automatically changed to EST / DST last weekend.

You want EST all the time without the DST adjustment (if in being)

First you need to establish the algorithm used for deciding when to 
change the clocks.

Here's a chart I got from:

http://geography.about.com/cs/daylightsavings/a/dst.htm

      Year Spring Forward Fall Back
      2004 2 a.m. April 4 2 a.m. Oct. 31
      2005 2 a.m. April 3 2 a.m. Oct. 30
      2006 2 a.m. April 2 2 a.m. Oct. 29
      2007 2 a.m. April 1 2 a.m. Oct. 28
      2008 2 a.m. April 6 2 a.m. Oct. 26
      2009 2 a.m. April 5 2 a.m. Oct. 25


From this chart, it looks like the appropriate dates for US are the 
first Sunday in April and last Sunday in October.

This gives me a formula as follows:

=IF(AND(DATE(YEAR(NOW()),4,8)-WEEKDAY(DATE(YEAR(NOW()),4,7))+"2:00:00">=D1,NOW()<=(DATE(YEAR(NOW()),11,8)-WEEKDAY(DATE(YEAR(NOW()),11,7))+"2:00:00")-7),NOW()-1/24,NOW())

In this formula:
=DATE(YEAR(NOW()),4,8)-WEEKDAY(DATE(YEAR(NOW()),4,7))+"2:00:00"
Returns 2:00 AM on the first Sunday in April of the current year

=(DATE(YEAR(NOW()),11,8)-WEEKDAY(DATE(YEAR(NOW()),11,7))+"2:00:00")-7
Returns 2:00 AM on last Sunday in October of the current year

If NOW() is between those dates then your system clock has advanced an 
hour and you need to deduct 1/24 from Now().

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments) 
available free to good homes. 


0
njharker (1646)
4/10/2004 12:38:05 AM
thankx a lot Mr.Harker
>-----Original Message-----
>Hi Himu!
>
>I think I'm reading this differently from Peo.
>
>What you are saying is that you are in the EST Zone and 
have system 
>settings that automatically changed to EST / DST last 
weekend.
>
>You want EST all the time without the DST adjustment (if 
in being)
>
>First you need to establish the algorithm used for 
deciding when to 
>change the clocks.
>
>Here's a chart I got from:
>
>http://geography.about.com/cs/daylightsavings/a/dst.htm
>
>      Year Spring Forward Fall Back
>      2004 2 a.m. April 4 2 a.m. Oct. 31
>      2005 2 a.m. April 3 2 a.m. Oct. 30
>      2006 2 a.m. April 2 2 a.m. Oct. 29
>      2007 2 a.m. April 1 2 a.m. Oct. 28
>      2008 2 a.m. April 6 2 a.m. Oct. 26
>      2009 2 a.m. April 5 2 a.m. Oct. 25
>
>
>From this chart, it looks like the appropriate dates for 
US are the 
>first Sunday in April and last Sunday in October.
>
>This gives me a formula as follows:
>
>=IF(AND(DATE(YEAR(NOW()),4,8)-WEEKDAY(DATE(YEAR(NOW
()),4,7))+"2:00:00">=D1,NOW()<=(DATE(YEAR(NOW()),11,8)-
WEEKDAY(DATE(YEAR(NOW()),11,7))+"2:00:00")-7),NOW()-
1/24,NOW())
>
>In this formula:
>=DATE(YEAR(NOW()),4,8)-WEEKDAY(DATE(YEAR(NOW()),4,7))
+"2:00:00"
>Returns 2:00 AM on the first Sunday in April of the 
current year
>
>=(DATE(YEAR(NOW()),11,8)-WEEKDAY(DATE(YEAR(NOW()),11,7))
+"2:00:00")-7
>Returns 2:00 AM on last Sunday in October of the current 
year
>
>If NOW() is between those dates then your system clock 
has advanced an 
>hour and you need to deduct 1/24 from Now().
>
>-- 
>Regards
>Norman Harker MVP (Excel)
>Sydney, Australia
>njharker@optusnet.com.au
>Excel and Word Function Lists (Classifications, Syntax 
and Arguments) 
>available free to good homes. 
>
>
>.
>
0
4/13/2004 5:19:59 PM
Hi Himu!

Thanks for thanks. I hope the formula was of use.

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments) 
available free to good homes. 


0
njharker (1646)
4/14/2004 2:46:08 AM
Reply:

Similar Artilces:

Excel 2003 chart plots wierd on Excel 2007, time value x-axis
Hey all, just upgraded. I have a chart with multiple time value series going out many years, some of the series have data going into the future a while and some only to the current date. I plot the data up to the current date to look at performance against target. With the recent upgrade to 2007, the entire series plots on the x-axis even if I select a subset for the time values on the axis format dialog box. Is this new behavior in 2007? Or did I have the axis as category in 2003 and it converted it when I shifted to 2007? I made this so long ago I don't recall the setup det...

time quick entry
I would like to do quick entry for times to the hundredth of a second (e.g., m:ss.00). I've used Chip Pearson's method for time quick entry as a starting point, and edited the Cases, but it's not working for me. I've included the code I've tried below. Thanks for any help, ~ Horatio Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub End If If Target.Cells.Count > 1 Then Exit Sub End If If Target.Value = "" Then ...

Help with DST
Greetings, I would like to know if you have place all the DST patches on the clients, servers, and apps but have not run the Exchange calendar clean up tool against your mailboxes or public folders, will you have any issues come fall. Becase we only publish 6 months worth of calendars will the apps and tasks have the right DST infomation because of the new CDO dll? If not why? Also if there will be issues and I need to run the tool can I run it after April 1st and not have the users notice any changes? Thanks, D On Wed, 28 Mar 2007 07:56:06 -0700, DW <DW@discussions.microsoft....

how to stop emails incoming its taking a long time to download
i got 39 messages incoming its taking a long time so how to stop them from incoming Best asked in the Outlook or Outlook Express usegroup? Eventually they're all going to come in anyhow unless you have a way to delete them off of the server. Remember, you can go do other things while the emails are downloading in the background, although surfing may be a bit slow. "emails incoming" wrote: > i got 39 messages incoming its taking a long time so how to stop them from > incoming ...

Opportunity est. close date workflow
How can I make a workflow rule when opportunity is created to set estimated close date 3 months after opportunity created date. I workflow manager I can't find est. close date field anywhere. Thanks, M This is certainly a problem for my company as well. Dates are not supported in workflow rules. The users will have to enter it manually, or you must set up this auto-population programatically. A way that I got over a similar situation: When the User opens a new Opportunity, they must select a "Service Type" from a picklist. When they do this, it triggers an "OnChang...

DST Exchange Tool : getting : 0X80004005 : Unable to find mailbox timezone error
Hi Running the tool for users on an Exchange 5.5 server, getting a lot of users with error : 0X80004005 : Unable to find mailbox timezone error... We know that these users have logged in outlook before, but don't use OWA in this org. Can anyone help... Have several thousand users to get trough... Thanks !!! -- If you know their time zone, just copy then to mailboxes_1.txt. Add server name and time zone. Use Excel fill down function, then save it as mailboxes_1.txt, run the batch file. On Sat, 24 Feb 2007 23:29:44 -0500, "Thinkpad21" <thinkpad21@yahoo.com> wrote:...

Word display a strange message while we open a file for the first time
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Dear all, <br> I have some iMac and user use it to be logged on a server. Network account are created on the server. Also their Home folder and documents are on the server. <br><br>When user opens a file (i think mainly the docx) their get that error message: <br><br>&quot;file&quot; is being used by &quot;another user&quot;. Do you want to make a copy? <br><br>We are all sure that no other user is reading or writing on that file. And this happen only wh...

adding racing times
I have a horse racing hobby and I'm trying to figure out how to add some times. Since Horses are timed in fifths of a second i am having some trouble. Example: 28 4/5 (currently I type 28.4) + 28 4/5 (28.4) = 57 3/5 but excel gets 56.8 because it adds in tenths. I know i can manually type in 28.8 + 28.8 and then convert back fifths and get the right answer but I like to copy the info verbatim from the racing paper...any suggestions? Thank you, this is my first time here and new to excel. --- ~~ Message posted from http://www.ExcelForum.com/ If you type 28 4/5 you can do the ...

email delievere dmultiple times
i have an Exchange 2003 FE-BE topology setup. i have one user who is receiving a single message repeatedly at various time intervals. i seem to remeber reading about this condition some time ago but can no longer fnid any reference to the issue in KB articles. anyone have any advice where to look? Lots of reasons for this, but if it's an external message, look in the SMTP protocol logs and see if the sending server is correctly ending the conversation. If the sending server doesn't believe the delivery was successful, it will try again. We often see this with bad firewall conf...

format a cell as running time
Hello All, I have a simple question I cannot figure out. I would like to keep a spreadsheet of my workouts(running) . I have columns "Total Time" and Split Time(s)" (multiple of these) How would I format a cell as running time? i.e. 22.20.93 and say one of the splits is 2.28.03 (two minutes etc...) Thanks for any suggestions, Homer One way: Format/Cell/Number/Custom mm:ss.00 then enter 2:28.03 In article <94D844C9-A8C9-42FC-91DF-BCDD252ACC6E@microsoft.com>, "Homer" <Homer@discussions.microsoft.com> wrote: > Hello All, > I hav...

Run-time error 2001 & cannot open form in design view
After saving some design changes on a fairly complex form I am now getting run time error 2001 and the form will not open. It will not open in design view either. I had only added a label and changed some formatting before saving the design change. This has happened before on this same form and I retrieved the old version from my back up. Has anybody else experienced this? Is there possibly a limit to how many sub-forms can be on a form that may be causing this? Ernie, I'm having the same problem with a main form that contains 10 tabbed subforms. I'm able to open ...

one file opens multiple times simultaneously
Hi, Can you help me with a file problem I am having. The Problem: Any excel file when opened the excel program opens multiple copies of it. I am not sure why this is happening. There are no links; they are not templates, etc. For example: one workbook opens 9 of the same workbook. Weird. I appreciate any guidance you can give. Run an updated anti virus and repair or reinstall the application If you want the technical term: It could be any number of things A globally applied VBA could do this just as well as a virus You can find Detect and Repair under the Help Menu. This will ...

calculate time
Hello, i've got a little problem with time. A2 = 22:00 ( start time ) b2 = 7:00 ( finish time ) c2 = 1:30 ( breaks ) d2 = 7:30 ( hours worked less breaks ) e2 = hours worked between 6pm and midnight ( formula required please ) f2 = hours worked between midnight and 6am ( formula required please ) g2 = hours worked between 6am and 6pm ( formula required please ) i've looked on the forums and found out how to workout how to calculate d2 but need formulas for e2,f2,g2 i'm sure this will be easy to someone but i'm clueless. thanks for any help given Hi, Try this in E...

Time Field in SmartList
I have a suggestion for Microsoft - I think that the time field available in GP under Sales Transactions in SmartList should be useable. Currently, when looking at that field, the value is always 12:00:00 AM, and doesn't display the time the order was created or printed, whichever it is inteded to track. We would like this feature, since we are currently trying to find an easier way to track how many orders our salespeople have typed up in the morning vs. in the afternoon for each day. If anyone knows an existing way to do this, please let us know. ---------------- This post is ...

Run-Time error 2220
I have an Access 97 database that is getting a Run-time error 2220 when I try to use it under my new XP operating system. Is there any known problems with trying to run Access 97 on XP? ...

Can time series be plotted as half solid, half dotted line?
Is there a way to plot a single time series as a half solid line for actual data and half dotted (or dashed) line for the forecast period? Thanks ! ~Carol Carol, Break up your table into 3 columns, like this: Date Actual Forecast Date1 Actual1 Date2 Actual2 Date3 Actual3 Date4 Actual4 Date5 Actual5 Actual5 Date6 Forecast1 Date7 Forecast2 Date8 Forecast3 Date9 Forecast4 Date10 Forecast5 Then you will have two lines to graph - and one can be set to solid, the other dashed, and they will ...

Conditional Multiple Time Ranges
I have pivot tables that pull from a Data Warehouse that regenerates during 6 time ranges each day. I want to put a message on each pivot table that displays only during these time ranges. I have succeeded in putting a running clock time on the sheet, but can't figure out how to display my text for these 6 ranges. For example, between 8:00 AM and 8:15 AM and 6 other ranges thereafter I want to display text in a cell on the sheet that provides a message to the user. How do I specify these ranges and how do I conditionally show the text for 6 ranges? Thanks for your he...

More DST Hell
As is evident from the replies to my post of 2/26, lots of folks are DST confused -including Microsoft. The site 'http://support.microsoft.com/gp/cp_dst' gives me links to 926666 when I input Exchange 2000 on W2K server(!?!). They won't directly tell you "no, you're just out of luck with E2k", but they bury the bad news in an article about E2k3SP2, where the inference is that they won't be updating the CDO.dll on that product. Here is what I told on of my many confused clients - Am I correct? "The only affect of not updating Exchange 2000 by paying th...

On Hand Quantity Point in Time
Ok, so I have figured out how to make a crystal report generate a on hand quantity for an item at any given time in the past. I have that now as a sub report and transfered the value in a variable to the main report to to perform other logic tasks but it has some issues. I can not sum or count any calculations for the report in total due to the nature in which the calculations are done so what I really need is someway for the SQL server to provide that answer. That is, given item X and date Y what was the quantity on hand on that day based on the sum of calculations and the adjusted be...

Working Time calculation
I would like to generate report for those who late to work. But the database time is in text format, so i change it to short time using code as below. Is it correct ? & how do i set a calculation for total time late in HH:MM:SS as my working time is 09:00 Format(IIf(IsNull([dbo_Punctual.TrTime]),#12/30/2010#,[dbo_Punctual.TrTime]),'Short Time') AS [TRANSACTION TIME] "Nancy Tang" <NancyTang@discussions.microsoft.com> wrote in message news:F1A7B165-2340-4BF7-8447-5C38B1D63EAA@microsoft.com... >I would like to generate report for those who late to ...

EST/DST Time
hi i would like to make a cell that shows the time in EST throughout the year. Note: i have normal time set in my "Time And Date Property", which means it changes time with daylight saving. Please help me find a soltion so that i get EST time in that excel worksheet all thoughout the year. thank you. What is your normal time, pacific? If so use =TEXT(NOW()+3/24,"hh:mm") mountain =TEXT(NOW()+2/24,"hh:mm") central =TEXT(NOW()+1/24,"hh:mm") use n/24 depending on where you are -- Regards, Peo Sjoblom "Himu" <cricket_life@hotmai...

ObjectDataSource Call MaxRowCount multiple times
If you have an ObjectDataSource and are using it to do the Page Count - it calls a method that querys the database to get this number. The problem is that everytime the user presses the page control at the bottom of the grid to get the next page or another page, it calls this method again. <asp:ObjectDataSource ID="ObjectDataSource1" EnablePaging="true" runat="server" SelectCountMethod="GetRowCount" SelectMethod="BindControl" TypeName="DAO" StartRowIndexParameterName="startRowIndex" Maxim...

summing time
Hi i am importing a worksheet from excel the workshhet contains data of work operations, faults that occurr the start time, end time and duration of the stoppage What i am trying to do sum the downtime against each operation for each fault the format of the data in the tables is start time /06/2007 20:32:12 end time 05/06/2007 20:39:25 duration i have wrote a query that sums the duration problem is the time format the answer returns 03/01/1900 11:32:12 i would like it to show hh:nn:ss 83:47:59 can anyone point me in the right direction please thanks kevin It appears you are usi...

can excel be a time clock or stop watch?
I have a project that I can't understand. I am tring to create a sign in and out workbook for my volunteers time totals. the volunteers will type in their names and click in and out. I want to make Excel keep track of the time, dates and volunteer names. is this possible? I want excel to also display the date and time on a top sheet. Am I asking to much out of what excel can do? This may be the answer you are looking for: http://office.microsoft.com/training/training.aspx?AssetID=RC060796661033&CTT=6&Origin=RP060796671033 It takes you thru a little traing session that seems t...

Scroll bar
I have a memo field with a lot of text in for each record. At the moment, unless the user has clicked on the memo field, the scroll bars to do show. This could sometimes suggest to an end ser who is unfamilier with my appliaction that there is no further text below the visable. Is there a way of making the verticle scroll bars appear all the time? RitchieJHicks wrote: > I have a memo field with a lot of text in for each record. > > At the moment, unless the user has clicked on the memo field, the > scroll bars to do show. This could sometimes suggest to an end ser > who is ...