Days360 function in Microsoft Access?

[Guest]

Is there a Days360 function in Access like there is in Excel? I know there
is a DateDiff function, but I need to find a way to get the difference
between two dates based on a 360-day calendar year. I want to do this in
Access.

 

[Guest]

Don't know about an existing function, but, if you know the math, write your
own function.

 

[Guest]

So what is the math? I need to do the same thing - DAYS360 in Access.

 

[david epsom dot com dot au]

This is the MS Excel version. For compatibility
with other standards, don't buy/sell/revalue at
the end of month.


d1% = Day(pDateFrom)
m1% = Month(pDateFrom)
y1% = Year(pDateFrom)
d2% = Day(pDateTo)
m2% = Month(pDateTo)
y2% = Year(pDateTo)

ElseIf sSimpleDayBasis = "7" Or sSimpleDayBasis = "Excel30" Then
'30/360 PSA 30, NASD 30, used only by MS Excel and Quantum


'handle last day in February (work with 1st day in march)
dtTemp1 = pDateFrom + 1

If (Day(dtTemp1) = 1) And (Month(dtTemp1) = 3) Then 'last day in Feb
d1 = 30
End If
If (d2 = 31) And (d1 = 30) Then
d2 = 30
End If

gfnDayDiff = ((y2% - y1%) * 360) + ((m2 - m1) * 30) + (d2% - d1%)

 

[Guest]

Hi,

It is possible to import Excel functions to Access. This, however, makes the
queries a bit slower.

1. In Access Visual Basic Editor (Reference to Excel)
2. Write your own function:
Function Days360(StarDate As Date, EndDate As Date) As Integer

Days360 = Excel.WorksheetFunction.Days360(StarDate, EndDate)

End Function

Good luck!!

 

[Guest]

Helo Czech
My english is very poor but I trie. I think that you need to calculate the
diference betwen to dates, presentig the result in years, months and days. I
suggest:

1º - calculate the number of days betwen dates; the year for this machinery
have 360 days. For this make a field for the anciant date and another for da
actualdate. Another fiel to calculate the total of days betwen the dates. the
formula is:


=((Year([actualdate])-year([anciantdate]))-1)*360+((12-month([anciantdate]))*30)+(30-day([anciantdate]))+((month([actualdate])-1)*30)+(day([actualdate])).

2º In the field for calculate the number of years put this:

=Int([the name of the fiel that calculate the total days]/360)

3º In the field to calculate the number of months put this:

=Int(([the name of the field that calculate total days]Resto 360)/30).
Note - Resto is the portuguese access function to name the remain on a
division. I d'ont know how to say in english. Is a artemetical function in
the Expression Bilder.

4º In the field to calculate the number of days, put:

=([the name of da field that calculate the total days] Resto 360) Resto 30.

This formulas functioning very well with me.

Good luck