Jeanniem
asked on
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").S
ActiveCell = ActiveSheet.Range("AE2")
' Next 4 lines are examples of what's in "AE2"
' 0
' 0
' 0
' 62507
Do While Not IsEmpty(ActiveCell.Offset(
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,TodaysDa
Case Is = 6
strdate = Right(agedate, 2) & "/" & (Left(agedate, 2)) & "/" _
& Mid(agedate, 3, 2)
newdate = DateValue(strdate)
ActiveCell.Offset(0, 4).FormulaR1C1 = "=DAYS360(newdate,TodaysDa
End Select
End Sub
Sub SADLP2()
Dim SADLP As Integer
Dim TodaysDate As Date
TodaysDate = ActiveSheet.Range("E2") ' 7/22/2007 is in cell E2
ActiveSheet.Range("AE2").S
ActiveCell = ActiveSheet.Range("AE2")
' Next 4 lines are examples of what's in "AE2"
' 0
' 0
' 0
' 62507
Do While Not IsEmpty(ActiveCell.Offset(
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,TodaysDa
Case Is = 6
strdate = Right(agedate, 2) & "/" & (Left(agedate, 2)) & "/" _
& Mid(agedate, 3, 2)
newdate = DateValue(strdate)
ActiveCell.Offset(0, 4).FormulaR1C1 = "=DAYS360(newdate,TodaysDa
End Select
End Sub
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
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
ASKER
Thanks Bob - I didn't test your solution because I had already accepted one from Jim - you guys rock!
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.Of
Else
cel.Offset(0, 4) = Application.Days360(CVDate
End If
Next cel
End Sub
Jim
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.Of
Else
cel.Offset(0, 4) = Application.Days360(CVDate
End If
Next cel
End Sub
Jim
ASKER
This solution didn't work, but the previous one did.
Jeanniem,
> This solution didn't work
Hmm, what did/didn't it do?
> but the previous one did
Great, glad to help!
Jim
> This solution didn't work
Hmm, what did/didn't it do?
> but the previous one did
Great, glad to help!
Jim
ASKER
It returns large negative numbers when the value is zero.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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,TodaysDa
to this:
ActiveCell.Offset(0, 4).FormulaR1C1 = Application.Days360(newdat
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