Days360 function in Microsoft Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
Don't know about an existing function, but, if you know the math, write your
own function.
 
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%)
 
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!!
 
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
 
Back
Top