Tricky one - Daylight Saving Time/DST/Wintertime and leap years. How to?

[Daniel86]

So.. here's a tricky one.

I have furiously been trying to solve this on my own, but without any luck so far!

Basically, I need to create a worksheet for hourly observations/time stamps that goes on for the whole of 2012 (Jan 1st - Dec 31st).
Also, I will need a similar worksheet for 2013 and 2014... but notice, that 2012 is a leap year.

But, how do I make Excel able to handle the one hour difference that occurs during Daylight Saving Time (DST) in the spring and again in the fall?

Have ANYONE encountered this challenge before?

Any help is very very welcome!

[FDibbins]

see if this post helps you out any?

http://www.excelforum.com/excel-work...ving-time.html

Edit: i found that link in excel's own help facility, an oft overlooked feature

[Daniel86]

It is an often overlooked feature, yes I totally agree :-)
However, there is another issue as well. If e.g. you have half hourly observations/time stamps - that gives you 17.520 observations.. and if you use the function of taking one day and add 1/48 the whole way down, eventually you will get a mismatch since 1/48 comes with an infinite number of decimals.
These decimals adds up to create a time mismatch over time, for each observatoin.. and if you have 17.000+ observations, this could give some trouble..!

Maybe the only prober solution would be to actually create a "static" worksheet, where you simply type in the date stamps manually? :D

[FDibbins]

if you rounddown() to 8 decimal places, over a 24 hr period it comes back to exactly 24 hrs