Calculating # of days

[kandg]

Is there a function in Filemaker to count the number of days when given a date range (similar to Excel's Days360 function)? Thanks for your help.

[Kurt Knippel]

EndDate - StartDate

[kandg]

Is this based on a 360 calendar year? If not, what can I do to have it calculate based on a 360 calendar year? Thanks.

[CobaltSky]

Kurt's formula will deliver a result based on the standard calendar rather than the standard 360-day accounting year.

The DAYS360 formula in Excel offer two methods of calculation, which can be controlled in Excel by the use of the optional third parameter, where TRUE invokes the European method and FALSE invokes the US NASD method, with the latter being the default.

A formula for calculating a days360 interval by the European method in FileMaker Pro is:

Min(Day(EndDate), 30) - Min(Day(StartDate), 30) + ((Month(EndDate) - Month(StartDate)) * 30) + ((Year(EndDate) - Year(StartDate)) * 360)

or, if you want the US (NASD) method, you should use:

Case(Day(EndDate) = 31 and Day(StartDate) < 30, 1, Min(Day(EndDate), 30)) - Min(Day(StartDate), 30) + ((Month(EndDate) + Case(Day(EndDate) = 31 and Day(StartDate) < 30, 1) - Month(StartDate)) * 30) + ((Year(EndDate) - Year(StartDate)) * 360)

where EndDate and StartDate are the names of date fields in your database.

If you want to choose the method on the fly, or to have it determined according to other variables in your database, that is possible also, but requires a slightly more complex formula.

[kandg]

Thanks, the US method works! I'm trying to follow your formula but I can't seem to understand how it works. If it isn't too much to ask, can you walk me through it? Thanks again.

[CobaltSky]

Ok. Both methods are essentially similar, so it's easier if you focus on the European method first, then look at the 'adjustments' required by the US/NASD option.

The essential principle of DAY360 is to ignore days above 30 in each month, then to treat each month (including Feb) as though it had 30 days (and therefore, the year as though it had 360) for calculation of calendar intervals.

The Day( ) function returns the day of the month component of a date. The Min( ) function chooses the lowest of two values, so Min(Day(Date), 30) converts the day-of-month to DAY360 format by making sure that it never goes over 30.

The European formula deals with Days first, then months and then years, so the first phrase "Min(Day(EndDate), 30) - Min(Day(StartDate), 30)" is simply finding out how many days apart (using the 30-day month principle) the start and end days are, in their respective months. This can be positive or negative, and works as an adjustment to the month differential which follows.

Next, the Month(Date) function returns the month number. so subtracting the start date from the end date and multiplying by thirty provides the number of days within the interval contributed by the month component of the date. That is achieved with the second phrase: "((Month(EndDate) - Month(StartDate)) * 30)".

The same principle is then applied to the year component, using the Year(Date) function and the 360 as multiplier. Hence "((Year(EndDate) - Year(StartDate)) * 360)".

When the three resulting values are summed, the month interval (now in days) serves as an adjustment to the year interval (also in days), and the day interval completes the adjustment. If the start date is after the end date, then a negative number is returned, which is in line with the accepted convention for 360date calculations.

The US/NASD calculation preserves all of the above, and adds a further adjustment, being that if the end date falls on the 31st and the start date is less than 30, the end date should be treated as being the first day of the following month (thus not be omitted from the interval). This requires that a longer statement for the End Date component of the formula be inserted, viz: "Case(Day(EndDate) = 31 and Day(StartDate) < 30, 1, Min(Day(EndDate), 30))".

Since the situation where the 31 is converted to a 1 is one where it must be regarded as the first day of the following month, a similar adjustment for the month end date must be inserted, viz: "+ Case(Day(EndDate) = 31 and Day(StartDate) < 30, 1)".

When the two case statements for End Day and End Month are inserted into the original (European) day360 formula, we arrive at the (rather more ungainly):

Case(Day(EndDate) = 31 and Day(StartDate) < 30, 1, Min(Day(EndDate), 30)) - Min(Day(StartDate), 30) + ((Month(EndDate) + Case(Day(EndDate) = 31 and Day(StartDate) < 30, 1) - Month(StartDate)) * 30) + ((Year(EndDate) - Year(StartDate)) * 360)

Which brings us to the formula which is successfully working for you. It's not pretty to look at, but it does the job...

Hope that makes it a little easier for you to see what is 'happening' within the formula?!

[Vaughan]

Ray -- your knowledge impresses me! But why does anybody need a 360 day year?

Thanks!

[CobaltSky]

Hi Vaughan,

The 360 day year principle is a standard calendar calculation methodology in accounting, and therefore appears in some high end accounting systems.

As I understand it, the rationale has to do with the principle of monthly payments or income (which are equal amounts twelve times a year, regardless of the actual size of the month they fall in) and the need to have some equitable principle for calculating pro-rata amounts for periods which do not resolve to a precise number of months.

So, using the DAY360 method, it doesn't matter which month you terminate your mortgage in or whatever, because all days of the year are given equal value, and the pro-rata adjustments translate the monthly payment amount into thirty equal day amounts regardless of the month (politely ignoring the odd days around the margins).

If you want a better explanation than that, I guess you'd need to ask an accountant (but don't be surprised if they bill you!)...

[kandg]

Thanks Ray for your very thorough explanation of the formula. I see it clearly now. Thanks again!

[eswanborg]

Holy cow - thanks for a fantastic explanation of what's going on there. I'm just starting to implement some accounting features and this is tremendously helpful. Thank you.