[AccessD] Days 360

Wed Jan 28 14:47:04 CST 2004

Doris,

Thanks for the help.  After sending the email, I had to talk with my boss
and asked her to go over the code also (she is not a programmer), but just
talking it through with her made me realize the mistake.  You just
confirmed it.

                    "Mike & Doris Manning"                                                                                                    
                    <mikedorism at adelphia.net>        To:     "'Access Developers discussion and problem solving'"                             
                    Sent by:                          <accessd at databaseadvisors.com>                                                          
                    accessd-bounces at databasead       cc:                                                                                      
                    visors.com                       Subject:     RE: [AccessD] Days 360                                                      

                    01/28/2004 02:09 PM                                                                                                       
                    Please respond to "Access                                                                                                 
                    Developers discussion and                                                                                                 
                    problem solving"                                                                                                          

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

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