How to calculate the number of hours in a day

There tends to be 24hours in a day. You can assume that and save processing.

I couldn't figure out what the code did, but I fixed the syntax for VB.  I'm not sure if these functions exist in VB, so they're probably going to crash.
TimeSpan offset1 = TimeZone.CurrentTimeZone.GetUtcOffset(targetday)

public function GetNumHourInDay(DateTime targetday ) as integer
            dim num as integer
            targetday = targetday.Date
            TimeSpan offset1 = TimeZone.CurrentTimeZone.GetUtcOffset(targetday)
            TimeSpan offset2 = TimeZone.CurrentTimeZone.GetUtcOffset(targetday.AddDays(2)) 'because the utc change a Day+1 hour + 3
            if (offset1 > offset2) then
                num = 25
            elseif (offset1 < offset2) then
                num = 23
            else
                num = 24
            end if
            GetNumHourInDay= num
end function
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

The TimeZone object is linked to .NET, so it won't work.

Thanks anyway

The only parameter is a date. I want to know the number of hour for the 8/4/2008 or the 14/14/2009.

Thank you

I'm not following what you're trying to do. Is that when 'Daylight Savings' occurs?
VB has a function called DateDiff() if you want to look into that.

Or, just return 24 everytime, you'll be right 363/365 % of the time, thats within 10% margin of error ;-)

I want a VBA function that return the number of hours contained in the specified date.

So yes, the daylight saving is important. If not, my function would have just returned 24 ;)

I want to know the number of hour for the 8/4/2008 or the  >>14/14/2009.<< ??

Now I am really confused, which month is the 14th?

gtgloner,

it's just an example, I want to know the number of hours in ANY date.

Thanks

It may be a local thing, and admittedly I have had a few, but I'm pretty sure there are 24 hours in a day where I come from.

Therefore:

Public Const NO_OF_HOURS_IN_DAY as Integer = 24

...should do it for you.

There are, as mentioned above, 2 days of the year for most countries when there are 23 and 25 hours respectively, but the net difference is zero, meaning every day of the year has an average of exactly 24 hours.


"Or, just return 24 everytime, you'll be right 363/365 % of the time, thats within 10% margin of error"

...actually is 0.54%, so quite acceptable really.


"The only parameter is a date. I want to know the number of hour for the 8/4/2008 or the 14/14/20092

...this would be 24 (see above), and probably 24 (should we decide to do away with the annoying habit of incrementing years at the end of the 12th month).


Hope this helps.

Jell

OK, I think I have got it now, you want something that will detect when we go on and off daylight saving time and show the hours in those days as being 23 or 25?

You just need to store the dates for DST (or find a function that gets them from someplace).  I know they moved American DST just so it could screw up my Outlook appointments.

Ok guys... I will put 24 and when we will have daylight saving and my company will loose millions I will send you the bill ;)

Seriously, I have the code in C#.NET in my question. I need the equivalent in VBA. That's it ;)

Well actually, you'll lose millions on the day with 23 hours, but make it all back again on the day with 25. Less a nominal amount of interest I suppose...

;)

J

jell,

haha, it don't work like that ;) The main problem is interaction between companies. It's in energy sector, so it's why this imply such big amount of money.

The system stop working that is. This happened in a previous project... sadly.

<$.02>
In Arizona, they fo not allow DST. There may be other states that do not. What about the world in general?

DST is made up. As ear as the Earth is concerned, there are 24 hours in a day. The Earth doesn't speed up or slow down.

And since the time shift occurrs at 2:00 AM, on a Sunday, it is doubtful that your company will lose millions of whatever it is you are afraid they will lose.
</$.02>

;) I jest.

Seriously though, there isn't the same classes available in VBA as there is in .Net

Another expert may know differently, but I think you'd have to write a function to calculate it manually which might be tricky due it only being applicable in Europe & North America - depends on the scope of your application I suppose.

What you could do, and I'd only recommend this if absolutely necessary, is to batch your C# code into a registered COM interface and use it in Excel.

The following, which I found in Wiki, would make a manual calc extremely though.

In a typical case where a one-hour shift occurs at 02:00 local time, in spring the clock jumps forward from 02:00 standard time to 03:00 DST and the day has 23 hours, whereas in autumn the clock jumps backward from 02:00 DST to 01:00 standard time, repeating that hour, and the day has 25 hours. A digital display of local time does not read 02:00 exactly, but instead jumps from 01:59:59.9 either forward to 03:00:00.0 or backward to 01:00:00.0. In this example, a location observing UTC+10 during standard time is at UTC+11 during DST; conversely, a location at UTC−10 during standard time is at UTC−9 during DST.

Clock shifts are usually scheduled near a weekend midnight to lessen disruption to weekday schedules. A one-hour shift is customary, but Australia's Lord Howe Island uses a half-hour shift.[78] Twenty-minute and two-hour shifts have been used in the past.

Coordination strategies differ when adjacent time zones shift clocks. The European Union shifts all at once, at 01:00 UTC; for example, Eastern European Time is always one hour ahead of Central European Time.[70] Most of North America shifts at 02:00 local time, so its zones do not shift at the same time; for example, Mountain Time can be temporarily either zero or two hours ahead of Pacific Time. Australian districts go even further and do not always agree on start and end dates; for example, to start DST in 2006 Tasmania shifted clocks forward on 1 October, Western Australia on 3 December, and the remaining DST-observing areas on 29 October.[79]

Start and end dates vary with location and year. Since 1996 European Summer Time has been observed from the last Sunday in March to the last Sunday in October; previously the rules were not uniform across the European Union.[70] Starting in 2007, most of the United States and Canada observe DST from the second Sunday in March to the first Sunday in November, almost two-thirds of the year.[80] The 2007 U.S. change was part of the Energy Policy Act of 2005; previously, from 1987 through 2006, the start and end dates were the first Sunday in April and the last Sunday in October, and Congress retains the right to go back to the previous dates once an energy-consumption study is done.[81]

Beginning and ending dates are the reverse in the southern hemisphere. For example, mainland Chile observes DST from the second Saturday in October to the second Saturday in March, with transitions at 24:00 local time.[82] The time difference between the United Kingdom and mainland Chile may therefore be three, four, or five hours, depending on the time of year.

 
Time zones often lie west of their idealized boundaries, resulting in year-round DST.Western China, Iceland, and other areas skew time zones westward, in effect observing DST year round without complications from clock shifts. For example, Saskatoon, Saskatchewan is at 106°39′W longitude, slightly west of center of the idealized Mountain Time Zone (105°W), but the time in Saskatchewan is Central Standard Time (90°W) year-round so Saskatoon is always about 67 minutes ahead of mean solar time.[83] Conversely, northeast India and a few other areas skew time zones eastward, in effect observing negative DST.[24] The United Kingdom and Ireland experimented with year-round DST from 1968 to 1971 but abandoned it because of its unpopularity, particularly in northern regions.[84]

Western France, Spain, and other areas skew time zones and shift clocks, in effect observing DST in winter with an extra hour in summer. For example, Nome, Alaska is at 165°24′W longitude, which is just west of center of the idealized Samoa Time Zone (165°W), but Nome observes Alaska Time (135°W) with DST, so it is slightly more than two hours ahead of the sun in winter and three in summer.[85]

DST is generally not observed near the equator, where sunrise times do not vary enough to justify it. Some countries observe it only in some regions; for example, southern Brazil observes it while equatorial Brazil does not.[86] Only a minority of the world's population uses DST because Asia and Africa generally do not observe it.


Jell

<$.02>This horse is dead.</$.02>

Badotz,

If the function has to work with DST, this is because it will be used in a country that follow DST.

I know DST is made up, and It's not the problem. The problem is that it exist.

You don't know anything about the company I'm working for... except that it's in energy sector. I can explain you why in details if you are interest, but in a private email.

If you can't just answer my question, please don't post at all. I use Expert Exchange to because it can save me a lot of time searching ;)

<$.02>This horse is dead.</$.02>


...so this parrot.   HELLOOOOOOOOOOOOOOOOOO Polly  !


;)

A Google search for

daylight savings time

turns up 3,610,000 English pages

The very first link provides a list of dates for the beginning and ending of DST from 3004 through 2010. It even has a calculator for years after 2011.

You do not need to calculate the dates, merely check them against a list.

Sorry, dates from 2004 through 3004.

aarrgghh - Dates from 2004 rthrough 2010. Where's my coffee?

Line 4 gets the year from the <input>
Lines 16 through 20 output the results

Chizi, interesting.

I'll check this now and tell you. If you are right, I split points between Badotz and you.

Hi,

You were almost right Chizi... But the function work perfectly. The reason is that this is not a "normal" day (from 0am to 0am), but a "gaz day" that starts at 6am (and then ends at 6am) ;)