• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1039
  • Last Modified:

DAYS360 VBA Excel Error

I am trying to convert a column of data that has either a zero or a string representing either a 5 digit date (example:  51007) or a 6 digit data (example 112906) into a date so that I can do a datediff to get the difference between two dates.  The following code compiles and runs, but I get a #NAME? error when it's is a 5 or 6 digit date (the code works when the value is a zero).  Note that I have given some example values for data in Excel cells in comment fields beginning with '.  Any help would be greatly appreciated.




Sub SADLP2()

Dim SADLP As Integer
Dim TodaysDate As Date
TodaysDate = ActiveSheet.Range("E2")   ' 7/22/2007 is in cell E2

ActiveSheet.Range("AE2").Select
ActiveCell = ActiveSheet.Range("AE2")
' Next 4 lines are examples of what's in "AE2"
' 0
' 0
' 0
' 62507

Do While Not IsEmpty(ActiveCell.Offset(1, -1))

If Val(ActiveCell.Value) = 0 Then
    ActiveCell.Offset(0, 4).FormulaR1C1 = "=DAYS360(RC[-29],RC[-30])"
'[-29] = 6/21/2007 & [-30] =7/22/2007


    ActiveCell.Offset(1, 0).Select
    GoTo Cont:
End If
        If Val(ActiveCell.Value) <> 0 Then fmtdate (ActiveCell.Value)
        ActiveCell.Offset(1, 0).Select
Cont:
  Loop
       
End Sub

Sub fmtdate(ByVal agedate)
Dim newdate As Date
Dim strdate As String
Dim length As Integer
TodaysDate = ActiveSheet.Range("E2")
length = Len(Trim(agedate))

Select Case length
    Case Is = 5
        strdate = Right(agedate, 2) & "/0" & (Left(agedate, 1)) & "/" _
         & Mid(agedate, 2, 2)
        newdate = DateValue(strdate)

       ActiveCell.Offset(0, 4).FormulaR1C1 = "=DAYS360(newdate,TodaysDate)" ' t
    Case Is = 6
        strdate = Right(agedate, 2) & "/" & (Left(agedate, 2)) & "/" _
       & Mid(agedate, 3, 2)
    newdate = DateValue(strdate)


        ActiveCell.Offset(0, 4).FormulaR1C1 = "=DAYS360(newdate,TodaysDate)"
   
End Select
       
End Sub


0
Jeanniem
Asked:
Jeanniem
  • 4
  • 3
1 Solution
 
jeveristCommented:
Hi Jeanniem,

It looks like you're trying to use VBA variables in a spreadsheet function.  Let's start by changing this:

        ActiveCell.Offset(0, 4).FormulaR1C1 = "=DAYS360(newdate,TodaysDate)"

to this:

        ActiveCell.Offset(0, 4).FormulaR1C1 = Application.Days360(newdate, TodaysDate)

and these:

        strdate = Right(agedate, 2) & "/0" & (Left(agedate, 1)) & "/" _
         & Mid(agedate, 2, 2)
         
        strdate = Right(agedate, 2) & "/" & (Left(agedate, 2)) & "/" _
       & Mid(agedate, 3, 2)

to this:

         strdate = Format(agedate, "00/00/00")

Jim
0
 
lynx20Commented:
Hi:

I'm not familiar with the accounting 30 day / 360 day calender but if you simply need the number of days between dates you could use:

ActiveCell.Offset(0, 4) = DateDiff(d,newdate, TodaysDate)

This also allows newdate to be after TodaysDate.  The result would be negative. [The d is a constant to use days as the interval].

Bob
0
 
JeanniemAuthor Commented:
Thanks Bob - I didn't test your solution because I had already accepted one from Jim - you guys rock!
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
jeveristCommented:
Jeanniem,

I think we can replace the two routines with one.  Try this:

Sub SADLP3()

Dim SADLP As Long, TodaysDate As Date
Dim ws As Worksheet, rng As Range, cel As Range

Set ws = ActiveSheet

TodaysDate = ws.Range("E2")   ' 7/22/2007 is in cell E2

Set rng = Range(ws.[AE2], ws.[AE2].End(xlDown))

For Each cel In rng
    If CLng(cel.Value) = 0 Then
        cel.Offset(0, 4) = Application.Days360(cel.Offset(0, -29), cel.Offset(0, -30))
    Else
        cel.Offset(0, 4) = Application.Days360(CVDate(Format(cel.Value, "00/00/00")), TodaysDate)
    End If
Next cel

End Sub

Jim
0
 
JeanniemAuthor Commented:
This solution didn't work, but the previous one did.
0
 
jeveristCommented:
Jeanniem,

>  This solution didn't work

Hmm, what did/didn't it do?

>   but the previous one did

Great, glad to help!

Jim
0
 
JeanniemAuthor Commented:
It returns large negative numbers when the value is zero.
0
 
jeveristCommented:
Jeanniem,

OK, it looks like the Offset is wrong.  Change this:

        cel.Offset(0, 4) = Application.Days360(cel.Offset(0, -29), cel.Offset(0, -30))

to this:

        cel.Offset(0, 4) = Application.Days360(cel.Offset(0, -25), cel.Offset(0, -26))

Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now