Its always surprising how a simply solution suddenly gets complicated! My
understanding of the concept of a 360 day year is that the year comprises 12
months of 30 days. If the month has either 31 days or 28/29 days in February,
then its assumed it has 30. I hadn't considered this when I responded. So, to
accomodate try:
Function Date360(FirstDate, SecondDate) As Integer
Dim FirstDay, SecondDay
Select Case Day(FirstDate)
Case 31
FirstDay = 30
Case 28, 29
If Month(FirstDate) = 2 Then
FirstDay = 30
Else
FirstDay = Day(FirstDate)
End If
Case Else
FirstDay = Day(FirstDate)
End Select
Select Case Day(SecondDate)
Case 31
SecondDay = 30
Case 28, 29
If Month(SecondDate) = 2 Then SecondDay = 30
Case Else
SecondDay = Day(SecondDate)
End Select
Date360 = ((DateDiff("m", FirstDate, SecondDate) - 1) * 30) + (30 -
FirstDay) + SecondDay
End Function
Try it and let me know if it still gives any incorrect answers so we can
adjust.
Cherers.
BW
"John Tripp" wrote:
> Thanks. Your routine gives some different results than Days360.
> eg:
> 1/31/2007,8/1/2007
> 2/28/2007,8/1/2007
> 3/31/2007,8/1/2007
> 4/30/2007,8/1/2007 is ok. It appears that date1 entries that end on other
> than the 30th are different.
>
> Date2 entries of other than 30 day month ends seem to be OK.
>
> John Tripp
>
>
> "BeWyched" wrote:
>
> > Hi John
> >
> > Try:
> >
> > Function Date360(FirstDate, SecondDate) as Integer
> > Date360 = (DateDiff("m", FirstDate, SecondDate) * 30 + (Day(SecondDate)-
> > Day(FirstDate))
> > End Function
> >
> > Calling the function, e.g.
> >
> > MsgBox date360(#01/01/2007#, #04/04/2007#)
> >
> > will give the day count based on a 360 day (12 x 30) accounting year.
> >
> > Cheers.
> >
> > BW
> >
> >
> > "John Tripp" wrote:
> >
> > > Does anyone have a good equivalent routine to the excel Days360 function? I
> > > am using Access 2003. Thanks, John
|
Similar Threads
