Daylight Savings Time Funcitions :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Daylight Savings Time Funcitions
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

mainstreet14534
Welcome to the Board


Joined: 20 Sep 2003
Posts: 2

Flag: Usa

Status: Offline

 Reply with quote  

Daylight Savings Time Funcitions

Howdy - First Post.

I have a need for a function or formula to determine if a date should have Daylight Saving Time (DST) applied. (e.g., Test(11/DD/YYYY)=Yes or =1)

I understand the 'rules to be': for areas of the country who observe DST, DST occurs between the 4th Sunday in October to the 4th Sunday in the following April. (The time switch occurs at 2 AM.)

I can obviously determine the applicable month ranges
(e.g., =IF((MONTH(A143)>=10)+(MONTH(A143)<=4),"DST month","")

For me the tricky part has been determining the applicable period corressponding to the 4th Sunday so that I can combine the tests.

I can determine what is the 4th Sunday (a date each month). However, I haven't determined when a month's date is >= the 4th Sunday.

I might be dense, but 'am having trouble.

Any ideas anyone?

Thanks!

BTW - Just bought Mr. Excel on Excel. I'm fairly adept, but it is still worth the investment!
_________________
----------------------------------------
MainStreet

Post Sat Sep 20, 2003 5:43 pm 
 View user's profile Send private message

Yogi Anand
MrExcel MVP


Joined: 13 Mar 2002
Posts: 5749
Location: Michigan USA

Status: Offline

 Reply with quote  

Re: Daylight Savings Time Funcitions

Hi there MainStreet:

Welcome to MrExcel Board!

Please look at the following simulation ...

Microsoft Excel - Book4___Running: xl97 : OS = Windows 98
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=

A
B
C
D
1
 MonthNumberFourthSundayOfTheMonthOfCurrentYear 
2
 101/26/03 
3
 404/27/03 
4
 1010/26/03 
Sheet2 

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


Would this do? If I have misunderstood your question -- my apologies!
_________________
Regards!
Yogi Anand

Post Sat Sep 20, 2003 6:44 pm 
 View user's profile Send private message

Aladin Akyurek
.


Joined: 15 Feb 2002
Posts: 14083
Location: The Hague
Flag: Blank

Status: Offline

 Reply with quote  

Re: Daylight Savings Time Funcitions

=--OR((MONTH(C2)<=4)*(C2<=DATE(YEAR(C2),4,7-WEEKDAY(DATE(YEAR(C2),4,1),2)+22)),(MONTH(C2)>=10)*(C2>=DATE(YEAR(C2),10,7-WEEKDAY(DATE(YEAR(C2),10,1),2)+22)))

which results either in 1 or 0. C2 houses a date to test.

You can format the formula cell as:

[=0]"";[=1]"DST month"

Post Sat Sep 20, 2003 8:24 pm 
 View user's profile Send private message

tbardoni
Board Master
Board  Master


Joined: 30 Aug 2002
Posts: 1613
Location: Rochester, MI USA
Flag: Usa

Status: Offline

 Reply with quote  

Re: Daylight Savings Time Funcitions

Yogi,
I'm not very good wit DATE formulas yet...if its not too much trouble, could you give a quick rundown of how your formula works...I can't seem to grasp it.

Thanks,
Todd
_________________
Board Nit
Dyslexics of the World Untie!

Post Sat Sep 20, 2003 10:01 pm 
 View user's profile Send private message Send e-mail AIM Address MSN Messenger

Ivan F Moala
MrExcel MVP


Joined: 11 Feb 2002
Posts: 3211
Location: Auckland, New Zealand
Flag: Newzealand

Status: Offline

 Reply with quote  

Basically Yogi's formula is calculating on a base Week day
default of 1 - 7 = Sunday - Saturday using american Dates
(to change to English others =(1&"/"&G2)+0-WEEKDAY(1&"/"&G2)+29)

4th sunday = 4 X 7 + Base Day = 1 +> +29

(B4&"/"&1)+0 coerces the date serial number for the 1st of the Month of the current year (US)


Adding them together yeilds 4th Sunday as a Serial number.

Note: Only applicable for North America as daylight savings is different for different continents eg.

  

ContinentCountryBeginning and ending days
AfricaEgyptStart: Last Friday in April
End: Last Thursday in September
 NamibiaStart: First Sunday in September
End: First Sunday in April
AsiaMost states of the former USSRStart: Last Sunday in March
End: Last Sunday in October
 IraqStart: April 1
End: October 1
 Israel(Estimate, Israel decides the dates every year)
Start: First Friday in April
End: First Friday in September
 Lebanon, KirgizstanStart: Last Sunday in March
End: Last Sunday in October
 MongoliaStart: Last Sunday in March
End: Last Sunday in September
 Palestine(Estimate)
Start: First Friday on or after 5 April
End: First Friday on or after 5 October
 SyriaStart: April 1
End: October 1
 IranStart: the first day of Farvardin
End: the first day of Mehr
AustralasiaAustralia - South Australia, Victoria, Australian Capital Territory, New South Wales, Lord Howe IslandStart: Last Sunday in October
(region that encompasses Australia, Tasmania, New Zealand, and other islands in the South Pacific)End: Last Sunday in March
 Australia - TasmaniaStart: First Sunday in October
End: Last Sunday in March
 FijiStart: First Sunday in November
End: Last Sunday in February
 New Zealand, ChathamStart: First Sunday in October
End: First Sunday on or after 5 March
 TongaStart: First Saturday in October
End: First Saturday on or after 15 April
EuropeEuropean Union, RussiaStart: Last Sunday in March
End: Last Sunday in October
 GreenlandStart: First Sunday in April
End: Last Sunday in October
North AmericaCanada, United States, Mexico, St. Johns, Bahamas, Turks and CaicosStart: First Sunday in April
End: Last Sunday in October
 CubaStart: April 1
End: Last Sunday in October
South AmericaBrazilStart: First Sunday in October
End: Last Sunday in February
 ChileStart: First Sunday on or after 9 October
End: First Sunday on or after 9 March
 FalklandsStart: First Sunday on or after 8 September
End: First Sunday on or after 6 April
 ParaguayStart: First Sunday in October
End: Last Saturday in February
AntarcticaAntarctica(same as Chile)

_________________
Kind Regards,
Ivan F Moala From the City of Sails

Post Sun Sep 21, 2003 5:06 am 
 View user's profile Send private message Send e-mail Visit poster's website

Yogi Anand
MrExcel MVP


Joined: 13 Mar 2002
Posts: 5749
Location: Michigan USA

Status: Offline

 Reply with quote  

Re: Daylight Savings Time Funcitions

Hi Ivan:

Thanks for great information beautifully presented.

Hi Todd:

I hope Ivan's explanation will do -- otherwise post back and then let us take it from there.
_________________
Regards!
Yogi Anand

Post Sun Sep 21, 2003 3:34 pm 
 View user's profile Send private message

mainstreet14534
Welcome to the Board


Joined: 20 Sep 2003
Posts: 2

Flag: Usa

Status: Offline

 Reply with quote  

Re: Daylight Savings Time Funcitions

Thanks everyone.

A fantastic and 'Elegant' solution!

I'll be using this in Excel for some trans-time zone coordination and will try and adopt it for MS Project as well.

---------------------
Dennis
_________________
----------------------------------------
MainStreet

Post Mon Sep 22, 2003 1:43 am 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.