Days in Month Function ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

Function to Calculate Number of Days

The User-Defined Function DaysM() given below can be used in calculations that involve the number of days of a particular month. Copy and Paste the following Code into a Global Module and save it in your Project.

Function VBA Code

Public Function DaysM(ByVal varDate) As Integer 
Dim intYear As Integer
Dim intmonth As Integer

On Error GoTo DaysM_Err

If Nz(varDate) = 0 Then
    DaysM = 0    
    Exit Function
End If

intYear = Year(varDate)
intmonth = Month(varDate)

DaysM = Day(DateSerial(intYear, intmonth + 1, 1) - 1)

DaysM_Exit:
Exit Function
DaysM_Err:
MsgBox Err.Description, , "DaysM()"
DaysM = 0
Resume DaysM_Exit
End Function

Syntax: X = DaysM(varDate)

Replace the varDate parameter with a valid Date. The Number of Days for the Month will be returned in Variable X.

The Parameter value can be a valid Date, a Date in Text format like "15-02-2008" or in its corresponding numeric value 39493.

If you would like to re-write the Function differently by adding a few extra lines of code, then you may replace the expression DaysM = Day(DateSerial(intYear, intmonth + 1, 1) - 1) with the following lines of code:

DaysM = Choose(intmonth, 31, 28 + IIf((intYear Mod 4) = 0, 1, 0), 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)

If intmonth=2 then
   Select Case (intYear Mod 400)
       Case 100, 200, 300
            DaysM = DaysM - 1
   End Select
End if

Usage Options

This Function can be used in VBA Routines, Queries, or Text Controls in Forms or Reports where the number of days of a particular month is involved in calculations.

Example: An Employee resumed duty after her vacation on 15-02-2008. To calculate the balance number of days, for her salary payment, one of the three Expressions given below can be used.

Dt = #02/15/2008#

BalDays = 1 + DateDiff("d", Dt, DateSerial(Year(Dt), Month(Dt) + 1, 1) - 1) 

or

BalDays =   1 + Day(DateSerial(Year(Dt), Month(Dt) + 1, 1) - 1) - Day(Dt) 

or

BalDays = 1 + DaysM(Dt) - Day(Dt)

The first two calculations are performed with Built-in Functions. With DaysM() Function, which uses the second expression for the main calculation, we could arrive at the same result with a lesser number of characters in the last expression.

Finding the Number of Days in a Month is not a big issue. We have learned simple rules to keep track of this, like 30 days in months 4, 6, 9, and 11 (April, June, September & November) and 28 days in February. When it comes to February only we need calculations, like Year/4=0, to find whether to add or not to add one more day to 28 days in February.

But, this needs correction when we enter into Centuries. During the Year 2000, we took 29 Days in February for calculations. But this has not happened in the years 1700, 1800, or 1900 and not going to happen in calculations that include the years 2100, 2200 & 2300 either.

We take approximately 365.25 days for a year, based on Earth's rotation time around the sun, and every 4th year is considered as a leap year with 366 days, adding 1 more day in February.

But, it is estimated that the Earth's exact rotation time around the sun is about 365 days 5 hours 48 minutes, and 45.5 seconds (i.e. 365.2422 days) only. We take about 0.0078 days more every year into our calculations and this value will add up to 3.12 extra days in about 400 years' time. To adjust this excess 3 days, all Century Years not evenly divisible by 400 are made common years, even though they are evenly divisible by 4.

The remaining 0.12 value becomes 1.2, in about 4000 years' time, and the year 4000 is not a leap year, even though it is evenly divisible by 400.

Who knows before that some other discovery will take us into re-working the whole thing again?

References: Microsoft Encarta Encyclopedia

History of Calendar.

The Roman Calendar

The original Roman calendar, introduced about the 7th century BC, had 10 months with 304 days in a year that began with March. Two more months, January and February, were added later in the 7th century BC, but because the months were only 29 or 30 days long, an extra month had to be inserted approximately every second year. The days of the month were designated by the awkward method of counting backward from three dates: the calends, or first of the month; the ides, or middle of the month, falling on the 13th of some months and the 15th of others; and the nones, or 9th day before the ides. The Roman calendar became hopelessly confused when officials to whom the addition of days and months was entrusted abused their authority to prolong their terms of office or to hasten or delay elections.

In 45 BC Julius Caesar, on the advice of the Greek astronomer Sosigenes (flourished 1st century BC), decided to use a purely solar calendar. This calendar, known as the Julian calendar, fixed the normal year at 365 days, and the leap year, every fourth year, at 366 days. Leap year is so named because the extra day causes any date after February in a leap year to "leap" over one day in the week and to occur two days later in the week than it did in the previous year, rather than one day later, as in a normal year. The Julian calendar also established the order of the months and the days of the week as they exist in present-day calendars.

In 44 BC Julius Caesar changed the name of the month Quintilis to Julius (July), after himself. The month Sextilis was renamed Augustus (August) in honor of Caesar Augustus, who succeeded Julius Caesar. Some authorities maintain that Augustus established the lengths of the months we use today.

The Gregorian Calendar.

The Julian year was 11 min and 14 sec longer than the solar year. This discrepancy accumulated until by 1582 the vernal equinox (see Ecliptic) occurred 10 days earlier, and Church holidays did not occur in the appropriate seasons. To make the vernal equinox occur on or about March 21, as it had in AD 325, the year of the First Council of Nicaea, Pope Gregory XIII issued a decree dropping 10 days from the calendar. To prevent further displacement he instituted a calendar, known as the Gregorian calendar, provided that century years divisible evenly by 400 should be leap years and that all other century years should be common years. Thus, 1600 was a leap year, but 1700 and 1800 were common years.

The Gregorian calendar, or the New Style calendar, was slowly adopted throughout Europe. It is used today throughout most of the Western world and in parts of Asia. When the Gregorian calendar was adopted in Great Britain in 1752, a correction of 11 days was necessary; the day after September 2, 1752, became September 14. Britain also adopted January 1 as the day when a new year begins. The Soviet Union adopted the Gregorian calendar in 1918, and Greece adopted it in 1923 for civil purposes, but many countries affiliated with the Greek Church retain the Julian, or Old Style, calendar for the celebration of Church feasts.

The Gregorian calendar is also called the Christian calendar because it uses the birth of Jesus Christ as a starting date. Dates of the Christian era (see Chronology) are often designated AD (Latin anno domini, "in the year of our Lord") and BC (before Christ). Although the birth of Christ was originally given as December 25, 1 BC, modern scholars now place it as about 4 BC.

Because the Gregorian calendar still entails months of unequal length, so that the dates and days of the week vary through time, numerous proposals have been made for a more practical, reformed calendar. Such proposals include a fixed calendar of 13 equal months and a universal calendar of four identical quarterly periods.

Source: Microsoft Encarta Encyclopedia