Formula need with date span breakdown
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22191
Printed Date: 05 May 2024 at 9:11pm
Topic: Formula need with date span breakdown
Posted By: Kristi
Subject: Formula need with date span breakdown
Date Posted: 16 Jan 2017 at 10:22am
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.....
|
Replies:
Posted By: kevlray
Date Posted: 17 Jan 2017 at 4:27am
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.
|
Posted By: hilfy
Date Posted: 17 Jan 2017 at 4:30am
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
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: Kristi
Date Posted: 17 Jan 2017 at 4:45am
|
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.
|
Posted By: hilfy
Date Posted: 17 Jan 2017 at 6:32am
That's odd. Would you copy your formula and paste it here, please?
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: Kristi
Date Posted: 17 Jan 2017 at 6:38am
(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'
|
Posted By: hilfy
Date Posted: 17 Jan 2017 at 7:02am
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
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: Kristi
Date Posted: 17 Jan 2017 at 7:09am
hilfy, you ROCK! That fixed it!
THANK YOU! THANK YOU! THANK YOU!
|
Posted By: hilfy
Date Posted: 17 Jan 2017 at 7:27am
Glad I could help!
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
|