[AccessD] Days 360

Access starts counting with "11/30/2003 12:00AM" whereas Excel doesn't
include "11/30/2003" in the count.  Just drop the +1 from your MyTotalDays
calculation and you'll be okay.

Doris Manning
Database Administrator
Hargrove Inc.
www.hargroveinc.com


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
jeffrey.demulling at usbank.com
Sent: Wednesday, January 28, 2004 2:23 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Days 360



I have the following function that is used to calculate the number of days
based on a 360 day year.  When I compare the results to Excel's Days360
formula I get a different result.

The start date I am using is 11/30/2003 and an end date of 12/31/2003

My function returns 31 days and Excel returns 30 days.

Can anyone see/tell me why I am getting a different result.

TIA

Jeff

Function CalcDays360(mystartdate As Date, myEndDate As Date) As Double Dim
mytotaldays

mytotaldays = 0

Select Case DateDiff("m", mystartdate, myEndDate)
    Case 0 'Both dates are in the same month
        If myEndDate <> DateAdd("m", 1, DateSerial(Year(myEndDate),
Month(myEndDate), 1)) - 1 Then
            mytotaldays = DateDiff("d", mystartdate, myEndDate) + 1
        Else
            If Day(mystartdate) <> 31 Then
                mytotaldays = 30 - Day(mystartdate) + 1
            Else
                mytotaldays = 1
            End If
        End If
    Case Else 'Dates are not in the same month
        'Determine Number of Days in the first month
        If Day(mystartdate) <> 31 Then
            mytotaldays = 30 - Day(mystartdate) + 1
        Else
            mytotaldays = 1
        End If

        'Add Number of Days in the second month
        If myEndDate = DateAdd("m", 1, DateSerial(Year(myEndDate),
Month(myEndDate), 1)) - 1 Or Day(myendate) = 30 Then
            mytotaldays = mytotaldays + 30
        Else
            mytotaldays = mytotaldays + Day(myEndDate)
        End If

        'Add 30 days to total number for each whole months between dates
        mytotaldays = mytotaldays + (30 * (DateDiff("m", mystartdate,
myEndDate) - 1))
End Select

CalcDays360 = mytotaldays
End Function



_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com