Date Functions

Example 1: Deriving 2 from "Feb" or 4 from "Apr" Etc

Assume you are at a form with a field named StartDate which contains the month in name format. The function for performing this operation:

Me.StartDate = Month(CDate("1-" & Me.StartDate & "-1952")

Example 2: Calculating Number of Months from One Date to the Next

AND Have it Not "Round Off" (Eg., 12-20-2002 to 7-1-2002 should be 6 months, not 7)

The formula--typed as the CONTROL SOURCE for a text box--for calculating how many months from one date to the next normally would be =DateDiff("m",[start_date],[end_date]). Problem is, this function as is only is concerned with each change in months. Therefore, 12/31/2002 to 2/1/2003 would return a value of 2 months, when obviously it's only 1 month.

To fix this, you should compare the day of the month between the 2 dates, and if the "to" date has an earlier day than the "from" day, subtract 1 from the result. Thus the syntax would be like this:

=IIf((Day([end_date])<Day([start_date])),DateDiff("m",[start_date],[end_date])-1,DateDiff("m",[start_date],[end_date))

The function I used had the current date ( date() ) as the end date, and a field on the continuous form called start_date as the start date. The function looked like this:

=IIf((Day(Date())<Day([Start_Date])),DateDiff("m",[Start_Date],Date())-1,DateDiff("m",[Start_Date],Date()))