Daylight saving time

**nsv** · 06-29-2007, 08:46 AM

I need to correct some UTC time stamps; and in my time zone that means to add 2 hours in summer and 1 hour in winter.
Is there an Excel function that recognizes if a date is within daylight saving or not?

NSV

**daddylonglegs** · 06-29-2007, 09:08 AM

Given that daylight saving starts at 01:00 on the last Sunday in March and ends at 01:00 on the last Sunday in October if you have a timestamp, including time and date, in A1 this formula will add 1 hour in winter and 2 hours in summer, for any date

=IF(A1="","",IF(AND(A1>DATE(YEAR(A1),4,1-WEEKDAY(DATE(YEAR(A1),4,0)))+1/24,A1<DATE(YEAR(A1),11,1-WEEKDAY(DATE(YEAR(A1),11,0)))+1/24),2,1)/24+A1)

edit: here's a possible alternative formula, a little shorter but perhaps less transparent....

=IF(A1="","",IF(PRODUCT(A1-DATE(YEAR(A1),{4,11},1-WEEKDAY(DATE(YEAR(A1),{4,11},0)))-1/24),1,2)/24+A1)

**nsv** · 07-02-2007, 05:25 AM

Thanks a lot for the help!!

Just one more question: How does the first IF work - what does it mean that A1 = "","""? I have never seen that before.

I could not get the alternative solution to work as it apparently adds only 1 hour regardless of the time of year. I'll look a bit more into it when I get some time - I like the short solutions.

NSV

**Dav** · 07-02-2007, 07:08 AM

the first bit, just confirms that you have a value in a1, if you dont it leaves it blank. If you always have values the formula could be shortened to exclude this, but it is more elegant to leave it in.

Regards

Dav

**nsv** · 07-02-2007, 09:11 AM

ooops, I see it now; confusion caused by my using semicolon is stead of comma...

**daddylonglegs** · 07-02-2007, 03:39 PM

Originally Posted by nsv

I could not get the alternative solution to work as it apparently adds only 1 hour regardless of the time of year. I'll look a bit more into it when I get some time - I like the short solutions.

I think it worked originally but I foolishly edited the formula to "improve" it and only broke it

This is the working shorter version

=IF(A1="","",IF(PRODUCT(A1-DATE(YEAR(A1),{4,11},1-WEEKDAY(DATE(YEAR(A1),{4,11},0)))-1/24)>0,1,2)/24+A1)

**nsv** · 07-03-2007, 10:35 AM

Thanks very much again daddylonglegs for your time. As I said, I prefer short solutions, and this new version works.
(now I just need to find out how :-) ... wonder what this {4,11} is...)

I have a hurdle each time I seek advice in this forum as my Excel version is in my national language. The easisest way to translate the formulas I pick up is to write a line in VBA stating: ActiveCell.Formula = "=IF(A1=..." and then almost everything is translated.
But the switch between commas and semicolon causes errors - in this case it writes =IF (A1=",",IF(PRODUCT...). It is, however, easily mended if you're aware of it.

**big_worm81** · 03-25-2011, 04:35 PM

How could that formula be modified to subtract 5 hours in the summer and subtract 4 hours in the winter from UTC?

**nsv** · 04-03-2011, 02:38 PM

=if(a1="","",if(product(a1-date(year(a1),{4,11},1-weekday(date(year(a1),{4,11},0)))-1/24)>0,4,5)/24+a1)

I didn't test it, but give it a try

NSV

**BobB33569** · 08-17-2011, 07:14 PM

Originally Posted by nsv

=if(a1="","",if(product(a1-date(year(a1),{4,11},1-weekday(date(year(a1),{4,11},0)))-1/24)>0,4,5)/24+a1)

I didn't test it, but give it a try

NSV

How would this work if Daylight time:
Starts: Second Sunday in March
Ends: First Sunday in November
Time: 2 am local time

**lesdupre** · 06-20-2012, 09:07 AM

what does {4,11} mean?
how to express this in macro form?

**arlu1201** · 06-20-2012, 09:51 AM

Lesdupre,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

**mellaws** · 09-04-2016, 12:35 PM

Originally Posted by daddylonglegs

edit: here's a possible alternative formula, a little shorter but perhaps less transparent....

=IF(A1="","",IF(PRODUCT(A1-DATE(YEAR(A1),{4,11},1-WEEKDAY(DATE(YEAR(A1),{4,11},0)))-1/24),1,2)/24+A1)

This is an old thread but has recently become relevant to my needs. I removed the check for a blank cell, and it works as expected for CST time zone (Mexico City: GMT-5 summer, GMT-6 winter).

Please Login or Register  to view this content.

I can't figure out what the PRODUCT function arguments are. There doesn't appear to be an argument separator involved.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Did I make a mistake when amending the code? Thanks.

**protonLeah** · 09-04-2016, 02:57 PM

mellaws,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

**mellaws** · 09-04-2016, 04:13 PM

Originally Posted by protonLeah

mellaws,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Your rules certainly work against making this forum useful.