Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: Days360 Function Returns Incorrect Days Sometimes

    Hi All,

    Using Access 2013.

    I created an Excel Application and imported the Days360 Worksheet Function to Access.

    But some of the values returned are not correct and some are

    For example
    BeginDate....EndDate....D360
    ---------------------------------
    11/2/2011, 4/30/2012....179 (Should be 180)
    11/1/2010...10/31/2011..360 (Correct)
    2/1/2012...7/31/2012.....180 (Correct)

    Thoughts on why this happening and how I can defeat it?

    thx
    w

    Code:
    Option Compare Database
    
    Function D360(startdate As Date, _
                         enddate As Date)
                  
        Dim objXL As Object
        Dim num As Integer
        
        Set objXL = GetXLApp()
        num = objXL.WorksheetFunction.Days360(startdate, enddate)
        D360 = num
    
    End Function
    
    Private Function GetXLApp()
    
        Dim objExcel As Object
        Set objExcel = CreateObject("Excel.Application")
        Set GetXLApp = objExcel
        
        Set objExcel = Nothing
        
    End Function
    Last edited by goss; 03-18-14 at 18:09. Reason: Update

  2. #2
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    I tried adding the option Boolean argument to the Days360 Function, I tried both True and false. Still returns 179 days either way in the example data above

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I compute 178 days for your example. Note that November 2 means that the month is incomplete so April 30 meets the "push forward" rule.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks Pat,

    Apologies,

    That first sample should read 11/1/2011...4/30/2012
    The answer I need is 180

    thx
    w

  5. #5
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Here is the logic I ended up with

    =IF(DAYS360(C2,D2,FALSE)<0,0,IF(DAY(D2)=30,DAYS360 (C2,D2,FALSE)+1,DAYS360(C2,D2,FALSE)))

    Does everyone handle with approx. the same logic? I'm surprised I was not able to find more on Google about this.

    thx
    w

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Please check out the attached Excel spreadsheet. A2 is unlocked, but the rest of the sheet is locked (for simplicity only, unlock if you want to change anything).

    I think that the values being provided by the function call are correct, since they match what Excel produces for me.

    -PatP
    Attached Thumbnails Attached Thumbnails Days360 Demo.jpg  
    Attached Files Attached Files
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •