[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

DAYS360 VBA Excel Error

Posted on 2007-08-08
8
Medium Priority
?
1,033 Views
Last Modified: 2012-06-27
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
Comment
Question by:Jeanniem
  • 4
  • 3
8 Comments
 
LVL 38

Expert Comment

by:jeverist
ID: 19657281
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
 
LVL 9

Expert Comment

by:lynx20
ID: 19657463
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
 

Author Comment

by:Jeanniem
ID: 19657484
Thanks Bob - I didn't test your solution because I had already accepted one from Jim - you guys rock!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 38

Expert Comment

by:jeverist
ID: 19657485
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
 

Author Comment

by:Jeanniem
ID: 19657511
This solution didn't work, but the previous one did.
0
 
LVL 38

Expert Comment

by:jeverist
ID: 19657595
Jeanniem,

>  This solution didn't work

Hmm, what did/didn't it do?

>   but the previous one did

Great, glad to help!

Jim
0
 

Author Comment

by:Jeanniem
ID: 19657711
It returns large negative numbers when the value is zero.
0
 
LVL 38

Accepted Solution

by:
jeverist earned 2000 total points
ID: 19658257
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question