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 FunctionSyntax: 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 its corresponding numeric value 39493.
If you would like to rewrite 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 ifUsage 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 using built-in functions. The `DaysM()` function, which uses the second expression for its primary calculation, produces the same result with fewer characters in the final expression.
Determining the number of days in a month is generally straightforward. We commonly use simple rules to remember that April, June, September, and November (months 4, 6, 9, and 11) have 30 days, while February has 28 days. February alone requires additional calculations to determine whether an extra day should be added in a leap year. A commonly used rule is that a year evenly divisible by 4 is treated as a leap year.
However, this rule requires refinement when dealing with century years. For example, February had 29 days in the year 2000 because it was a leap year. In contrast, the years 1700, 1800, and 1900 were not leap years, despite being evenly divisible by 4. Similarly, the years 2100, 2200, and 2300 will also be treated as common years.
The leap year rule is based on the Earth's orbital period around the Sun. A calendar year is commonly approximated as 365.25 days, with every fourth year designated as a leap year containing 366 days by adding one extra day to February.
However, the Earth's actual orbital period is approximately 365 days, 5 hours, 48 minutes, and 45.5 seconds (365.2422 days). Using an average of 365.25 days takes an excess of approximately 0.0078 days each year. Over a period of about 400 years, this accumulates to approximately 3.12 extra days. To compensate for this excess, century years that are not evenly divisible by 400 are treated as common years, even though they are evenly divisible by 4.
The remaining excess of approximately 0.12 days accumulates to about 1.2 days over 4,000 years. Consequently, under this extended rule, the year 4000 is treated as a common year, even though it is evenly divisible by 400.
Who knows, before then, another scientific discovery may require us to revise the entire system once again.
References: Microsoft Encarta Encyclopedia
History of Calendar.
The Roman Calendar
The original Roman calendar, introduced around the 7th century BC, consisted of 10 months and a Total of 304 days, with the year beginning in March. Later in the same century, two additional months—January and February—were added. However, because the months contained only 29 or 30 days, an extra month had to be inserted approximately every second year to keep the calendar aligned with the seasons.
Days within each month were identified using the Roman system of counting backward from three fixed dates: the Calends, the first day of the month; the Nones, the ninth day before the Ides; and the Ides, which fell on the 13th day in some months and the 15th day in others. Over time, the Roman calendar became increasingly disordered because the officials responsible for adding days and months often manipulated the calendar to extend their terms of office or to hasten or delay elections.
In 45 BC, Julius Caesar, advised by the Greek astronomer Sosigenes (flourished in the 1st century BC), introduced a purely solar calendar. This system, known as the Julian calendar, established the common year as 365 days and designated every fourth year as a leap year with 366 days. The term leap year originates from the effect of the extra day in February, which causes dates after February to occur two weekdays later than in the previous year, rather than one weekday later as in a common year. The Julian calendar also established the order of the months and the seven-day week that form the basis of the modern calendar.
In 44 BC, Julius Caesar renamed the month Quintilis to Julius (July) in his own honor. Later, the month Sextilis was renamed Augustus (August) to honor Caesar Augustus, Julius Caesar's successor. Some authorities maintain that Augustus also established the lengths of the months as they are used today.
The Gregorian Calendar.
The Julian year was 11 minutes and 14 seconds 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, which 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
Earlier Post Link References:
