Days in current year

I

Ian

I have an expression which includes the number "365"
allowing me to get a daily value for an annual figure.
The only problem is if it's a leap year the result won't
be quite correct. Is there any way I can replace the
fixed number with some kind of function or such that will
include the correct number of days in the current year.

Thanks
 
N

Nick in Tokyo

I don't know iff access allows this but if I was coding it I would put in a
function where you minus a known leap year (199 off your year and then mod
divide (%) by 4. If it divides cleanly then it's a leapyear. I don't know
Access syntax but the logical equivalent of...

if ((2004 - year_value) % 4 == 0)
then num_days = 366
else num_days = 355
 
V

Van T. Dinh

3 different ways:

From Debug window:

?(#12/31/2004#) - (#12/31/2003#)
366

1. Subtract the last day of last year from last day of
current year



?IsDate("29/02/2003")
False

?IsDate("29/02/2004")
True

2. Check if 29/Feb/current year is valid or not. Note
that the string has to match the date setting in your
Regional Settings (dd/mm/yyyy for my PC).




?CInt(Format(#12/31/2004#, "y"))
366

Format the last date of current year to "day of year" (a
String) and then convert to Integer (or Long)

HTH
Van T. Dinh
MVP (Access)
 
T

Terry Kreft

Nick,
Your definition of a leap year is wrong.

If the year
is divisble by 4 then it is a leap year
unless
it is divisble by 100 when it isn't a leap year
unless
it is divisble by 400 when it is a leap year

So
Function IsLeapYear(YearIn As Integer) As Boolean
IsLeapYear = ((YearIn Mod 4 = 0) And (YearIn Mod 100 <> 0)) Or (YearIn
Mod 400 = 0)
End Function

correctly calculates if a given year is a leap year

to test this you can run

Function TestIsLeapYear()
Dim intX As Integer

For intX = 1900 To 2020
Debug.Print intX; " is "; IIf(IsLeapYear(intX), "", " not "); " a leap
year"
Next
End Function

particularly note that 1900 (divisble by 4 but also divisble by 100) is not
a leap year but 2000 (divisble by 400) is.
 
R

Rick Brandt

Terry Kreft said:
Nick,
Your definition of a leap year is wrong.

If the year
is divisble by 4 then it is a leap year
unless
it is divisble by 100 when it isn't a leap year
unless
it is divisble by 400 when it is a leap year

So
Function IsLeapYear(YearIn As Integer) As Boolean
IsLeapYear = ((YearIn Mod 4 = 0) And (YearIn Mod 100 <> 0)) Or (YearIn
Mod 400 = 0)
End Function

correctly calculates if a given year is a leap year
Click to expand...

One could also use...

Function IsLeapYear(YearIn As Integer) As Boolean
IsLeapYear = (Month(DateSerial(YearIn,2,29))=2)
End Function
 
T

Terry Kreft

Correct, but your function depends on a convention raised as a consequence
of the rule rather than on the rule itself, but I think it's a pretty safe
bet that the convention won't change <g>.

And to prove they are functionally equivalent

Function IsLeapYear(YearIn As Integer) As Boolean
IsLeapYear = (((YearIn Mod 4 = 0) And (YearIn Mod 100 <> 0)) Or (YearIn
Mod 400 = 0))
End Function

Function IsLeapYear2(YearIn As Integer) As Boolean
IsLeapYear2 = (Month(DateSerial(YearIn, 2, 29)) = 2)
End Function

Function TestIsLeapYear()
Dim intX As Integer

For intX = 1900 To 2020
If IsLeapYear(intX) <> IsLeapYear2(intX) Then
' never gets printed
Debug.Print "whoops on "; intX
End If
Next
End Function
 
R

Rick Brandt

Terry Kreft said:
Correct, but your function depends on a convention raised as a consequence
of the rule rather than on the rule itself, but I think it's a pretty safe
bet that the convention won't change <g>.
Click to expand...
[snip]

I actually considered that : )

I also considered afterwards that my method assumes that Microsoft has no
bugs in there implementation as it pertains to leap years.

If that were to happen I wonder if there would ever be a situation where it
would be better to be wrong yet in agreement with MS's implementation as
opposed to being correct and disagreeing with it?

Ah well; after Y2K let's all hope that "date considerations" are given
quite a bit more scrutiny than most others.
 
T

Terry Kreft

When you have to explain to a user why there's difference, in this situation
I'd be tempted to be wrong but in agreement with MS.

It took me long enough to explain why my implementation of the ISO date
standards was out of wack with the wall calendar one of my clients had, and
I'm sure they still didn't believe me after I'd finished anyway.


--
Terry Kreft
MVP Microsoft Access


If that were to happen I wonder if there would ever be a situation where it
would be better to be wrong yet in agreement with MS's implementation as
opposed to being correct and disagreeing with it?
Click to expand...
<SNIP>
 
Top