Formula need with date span breakdown

I need a formula that will do two things (an 'IF' formula):

IF table.end_date is not null then provide table.end_date *minus* table.start_days, broken out into years months & days
ELSE CurrentDate *minus* table.start_date, broken out into years months & days

This would take into account leap years and the accurate number of days per month.

So, it might says 3 years 2 months 23 days.....

The if part is easy (provided you slelect 'Default Values for Nulls' in the formula editor)

if isnull({table.end_date}) then ....

The next part is a little tricky since you you want years months and days.  But you can use the datediff function.   I think you would want to do the datediff by days, then do a mod of 365 to get the years and subtract amount off (the result of the mod * 365) that off to get the remaining days.  As far as the months and days, I am not sure.  Maybe there is a function I am not aware of.

This will get a bit complicated because of the Y/M/D breakdown. However, it can be done. Here's how I think it will work...

NumberVar startYear := Year({table.start_date});
NumberVar startMonth := Month({table.start_date});
NumberVar startDay := Day({table.start_date});
NumberVar endYear;
NumberVar endMonth;
NumberVar endDay;
DateVar endDate;
NumberVar yr;
NumberVar mth;
NumberVar dy;

If IsNull({table.end_date}) then
EndDate := CurrentDate;
else
EndDate := {table.end_date};

EndYear := Year(EndDate);
EndMonth := Month(EndDate);
EndDay := Day(EndDate);

yr := EndYear - StartYear;
if (StartMonth < EndMonth) then
mth := EndMonth - StartMonth
else
(
yr := yr - 1;
mth := EndMonth + (12 - StartMonth);
);


if StartDay < EndDay then
dy := EndDay - StartDay
else
(
if mth in [5, 7, 10, 12] then
    dy := EndDay + 30 - StartDay
else if mth <> 3 then
    dy := EndDay + 31 - StartDay
else
(
    dy := EndDay + 29 - StartDay;
    if yr mod 4 = 0 then
      dy := dy + 1; //leap year
);
);
ToText(yr, 0) + ' years ' + ToText(mth, 0) + ' months ' + ToText(dy, 0) + ' days'


-Dell

hilfy, your formula works great for those where an end_date exists. However, when it's NULL, instead of using the CurrentDate, it's just writing the year of the start_date, so it seems.

That's odd. Would you copy your formula and paste it here, please?

-Dell

(The only thing my Start Date & End Date formulas do is convert them both to Dates, as one's a DateTime & one's a string in the database.)
---------------------------------
NumberVar startYear := Year({@Start Date});
NumberVar startMonth := Month({@Start Date});
NumberVar startDay := Day({@Start Date});
NumberVar endYear;
NumberVar endMonth;
NumberVar endDay;
DateVar endDate;
NumberVar yr;
NumberVar mth;
NumberVar dy;

If IsNull({@End Date}) then
EndDate := CurrentDate
else
EndDate := {@End Date};

EndYear := Year(EndDate);
EndMonth := Month(EndDate);
EndDay := Day(EndDate);

yr := EndYear - StartYear;
if (StartMonth < EndMonth) then
mth := EndMonth - StartMonth
else
(
yr := yr - 1;
mth := EndMonth + (12 - StartMonth);
);


if StartDay < EndDay then
dy := EndDay - StartDay
else
(
if mth in [5, 7, 10, 12] then
    dy := EndDay + 30 - StartDay
else if mth <> 3 then
    dy := EndDay + 31 - StartDay
else
(
    dy := EndDay + 29 - StartDay;
    if yr mod 4 = 0 then
      dy := dy + 1; //leap year
);
);
ToText(yr, 0) + ' years ' + ToText(mth, 0) + ' months ' + ToText(dy, 0) + ' days'

It's possible that your {@End Date} formula never returns a null. So, I might change the first if statement in the formula to something like this:

If IsNull({Table.End_Date}) then   <--use the FIELD here instead of the formula.

-Dell

hilfy, you ROCK! That fixed it!

THANK YOU! THANK YOU! THANK YOU!

Glad I could help!

-Dell