NextTransRepeat
Back to ListDescription: Calculate the next date (>= minDate) for a recuring transaction.
FileMaker Prototype:
Set Variable [$res; ACF_Run("NextTransRepeat"; date_current; date_minDate; date_startdate; string_type; int_repeatCount)]
Category: EXCEL
Dependencies
- addMonthToDate: Add month to date. If startdate's day number is bigger tham the target month, adjust to last day in month.
- AdjustWorkDays: Adjust a given date to the next working day (Monday-Friday)
- AdjustWorkAndSaturays: Adjust a given date to the next working day if Sunday
Function source:
// Calculate the next date (>= minDate) for a recuring transaction.
function NextTransRepeat(date current, date minDate, date startdate, string type, int repeatCount)
// Ensure repeatCount is at least 1
if (repeatCount == 0) then
repeatCount = 1;
end if
// Determine the next transaction date based on the recurrence type
case
// One-off transactions: Set to a far-future date as they don’t repeat
:(type == "One-off")
current = date("2040-01-01", "%Y-%m-%d");
return current;
// Daily recurrence
:(type == "Daily")
if (current < minDate) then
// Increment until the date meets or exceeds minDate
while (current < minDate)
current = current + repeatCount;
end while
else
current = current + repeatCount;
end if
// Monday-Friday recurrence
:(type == "Mon-Fre")
if (current < minDate) then
while (current < minDate)
current = current + repeatCount;
current = AdjustWorkDays(current); // Skip to the next working day
end while
else
current = current + repeatCount;
current = AdjustWorkDays(current);
end if
// Monday-Saturday recurrence
:(type == "Mon-Sat")
if (current < minDate) then
while (current < minDate)
current = current + repeatCount;
current = AdjustWorkAndSaturays(current); // Skip to the next workday or Saturday
end while
else
current = current + repeatCount;
current = AdjustWorkAndSaturays(current);
end if
// Weekly recurrence
:(type == "Weekly")
if (current < minDate) then
while (current < minDate)
current = current + (7 * repeatCount); // Increment by weeks
end while
else
current = current + (7 * repeatCount);
end if
// Monthly recurrence
:(type == "Monthly")
if (current < minDate) then
while (current < minDate)
current = addMonthToDate(current, startdate, repeatCount); // Add months, handling varying month lengths
end while
else
current = addMonthToDate(current, startdate, repeatCount);
end if
// Annual recurrence
:(type == "Annualy")
if (current < minDate) then
while (current < minDate)
current = addMonthToDate(current, startdate, 12 * repeatCount); // Add years
end while
else
current = addMonthToDate(current, startdate, 12 * repeatCount);
end if
// Default case: Assume daily recurrence with repeatCount units
default
if (current < minDate) then
while (current < minDate)
current = current + repeatCount;
end while
else
current = current + repeatCount;
end if
end case
// Return the calculated next transaction date
return current;
end