Please for assistance.I am new in Designer and i have the following situation and do not know how to handle it:
date1 - date2 = filed1 result of field1: date1-date2 = x years, x months and x days)
date3 - date4 = filed2 (result od filed2: date3-date4= y years, y months and y days)
filed 3= (z years, z months, z days) (sum of result filed1 + filed2)
Thanks for the help!
Subject: Hint…
Calculate the number of days, then use division and Modulo to get years and month.
Divide by 365 and get the integer part of the value, that is the number of years.
Then use modulo to get the number of days left.
Divide by 30 to get months, and use Modulo to get the days left.
Subject: Hint…
Thank you Karl-Henry Martinsson
I found something on the Internet.
===================================================
I have fields “startdate1” and “enddate1”
Field “sum1” (computed) contains this value:
===================================================
Date1 :=@Date(startdate1);
Date2:=@Date(enddate1);
Y1:=@Year(Date1);
M1:=@Month(Date1);
D1:=@Day(Date1);
Y2:=@Year(Date2);
M2:=@Month(Date2);
D2:=@Day(Date2);
@If(D1<=D2; MX:=M2; MX:=M2-1);
AdjD2 := @If(@Modulo(Y2;4)=0;
@Select(MX;31; 29; 31;30;31;30;31;31;30;31;30;31);
@Select(MX;31; 28; 31;30;31;30;31;31;30;31;30;31));
@If(D1<=D2; D3:=D2-D1; D3:=D2+AdjD2-D1);
@If(D1<=D2; “”; M2:=M2-1);
@If(M1<=M2; M3:=M2-M1; M3:=M2+12-M1);
@If(M1<=M2; “”; Y2:=Y2-1);
Y3:=Y2-Y1;
M4:=M2;
D4:=@If(@Modulo(Y2;4)=0;
@Select(M4;31; 29; 31;30;31;30;31;31;30;31;30;31);
@Select(M4;31; 28; 31;30;31;30;31;31;30;31;30;31));
@If(D3 + 1 = D4; M3:=M3+1;“”);
@If(D3 + 1 = D4; D3:=0;D3:=D3+1);
@If(M3>=12; Y3:=Y3+1;“”);
@If(M3>=12; M3:=0;“”);
@Text(Y3) + " Years " + @Text(M3) + " Months " + @Text(D3) + " Days"
===================================================
for computed field “Years1” (last line of code)
@Text(Y3)
For field “Months1”
@Text(M3)
and field “days1”
@Text(D3)
if I have multiple fields:
Years1 | Months1 | Days1
Years2 | Months2 | Days2
Years3 | Months3 | Days3
How to sum that multiple fields?
But without wrong results eg: 2 years | 45 months | 87 days
Thank you very much
Subject: Code
You need to calculate the day count for the first date range, then for the second date range. When you have those, you calculate the components (year/month/day) for each day count. Finaly you add the two day counts together and calculate the components from that sum.
I posted some code on my blog, as it it easier to read the formatted text there:
http://blog.texasswede.com/code-get-date-range-as-years-months-and-days/
Subject: Use what’s built into the product
To begin with, it has to be said that there is no good (accurate) way to sum years, months and days across multiple time spans unless they’re contiguous. The problem is that months aren’t all the same length, so do you carry the days overflow at 30 days, 31 days or something else? With contiguous values, you know which month you’re talking about, so the choice is easy. With non-contiguous spans, though, the “how many days in a month” question is almost meaningless, so you need to pick an arbitrary value.
That said, you can let the date/time functions in the language you’re using do most of the heavy lifting for you when working with differences between dates. The leap year thing in the code you posted only appears to work because the year 2000 was an exception-to-the-exception year: it was divisible by 400, so it wasn’t skipped as most century years are. If you need to deal with historical dates, you’ll be wrong most of the time when you cross centuries. And things get much hairier if your values include times, where you need to take time zones and DST into account; a half-hour difference in the recorded time on the two days can give you an off-by-one error in the days calculation.
In general, the procedure for getting a date difference in years, months and days accurately looks like this:
First, split the two dates up into their component values. Then determine whether or not the end date comes earlier in its year than the start date or not. If it does not, you simply adjust the start date forward or the end date backwards by the difference in years. If the end date does come earlier in the year than the start date, you adjust it forward by one fewer years. Then do the same thing with the months. That just leaves you with a days value guaranteed to be less than one month, whatever the appropriate month length happens to be. In Formula Language, that looks something like this:
startDate := Time1;
endDate := Time2;
startDay := @Day(startDate);
endDay := @Day(endDate);
startMonth := @Month(StartDate);
endMonth := @Month(endDate);
startYear := @Year(startDate);
endYear := @Year(endDate);
lessAYear := @If(@Date(endYear; startMonth; startDay) > @Date(endDate); @False; @True);
yearsDiff := @If(lessAYear; endYear - startYear - 1; endYear - startYear);
@Set(“endDate”; @Adjust(endDate;-yearsDiff;0;0;0;0;0));
monthAdj := @If(startDay>endDay;-1;0);
monthsDiff := @If(lessAYear; (endMonth + 12) - startMonth + monthAdj; endMonth - startMonth + monthAdj);
@Set(“endDate”;@Adjust(endDate;0;-monthsDiff;0;0;0;0));
daysDiff := @Integer((endDate - startDate)/86400);
@Prompt([Ok];“”;@Text(yearsDiff) + “years, " + @Text(monthsDiff) + " months, and " + @Text(daysDiff) + " days.”)
A version of that formula was posted to the Notes and Domino R4.6 and R5 forum 11 years, 6 months and 29 days ago. (December 5, 2003 plus 11 years gives us December 5, 2014. December 5, 2014 plus 6 months gives us June 5, 2015. June 5, 2015 plus 29 days gives us July 4, 2015.) In this case, I decided (for no good reason) to adjust the end date backwards rather than adjust the start date forwards. They’re absolutely equivalent (provided that the @Adjust function handles things like the Julian-to-Gregorian calendar change correctly, which may be troublesome to historians). It uses @Set because reassignment to a variable in Formula Language could not be done using the assignment operator (:=) before Notes and Domino 6. For the same reason, @While wasn’t available. With @While, the Formula would look suspiciously like the Lotusscript function below:
startDate := Time1;
endDate := Time2;
@While(startDate < endDate; @Adjust(startDate;1;0;0;0;0;0); counter := counter + 1);
@Adjust(startDate;-1;0;0;0;0;0);
years := counter - 1;
counter := 0;
@While(startDate < endDate; @Adjust(startDate;0;1;0;0;0;0); counter := counter + 1);
@Adjust(startDate;0;-1;0;0;0;0);
months := counter - 1;
counter := 0;
@While(startDate < endDate; @Adjust(startDate;0;0;1;0;0;0); counter := counter + 1);
@Adjust(startDate;0;0;-1;0;0;0);
days := counter - 1;
counter := 0;
…and so on. It’s not necessarily better or faster, but it’s a lot easier on the brain, and a lot easier to extend to hours, minutes and seconds if you need to.
In LotusScript, the operation would be similar. Failing to use the AdjustWhatever methods of the NotesDateTime object will result in errors. Some parts of the calculation involve Lotusscript Date variants; that can’t be helped because there are no appropriate methods for getting the year, month or day directly from a NotesDateTime object. You can pull the values as NotesDateTime from your fields using the DateValue method of the corresponding NotesItem.
This function returns a list of integers with “years”, “months” and “days” values (those are the actual list tags). It can be extended using the same principles to return “hours”, “minutes” and “seconds” list members; that will be left as an exercise for the student, as they say. There is a Boolean switch to ignore the time values if you want any part of a day to register as a day for counting purposes; it’s really sort of silly unless this is extended to also return the time components, but you may not waant to count a fraction of a day as a full day either, so…
Function DateDifferenceYMD(startDT As NotesDateTime, endDT As NotesDateTime, ignoreTimes As Boolean) As Variant
'Returns a List of Integers with a "years", "months" and "days" tags
Dim componentsList List As Integer
Dim internalStartDate As NotesDateTime, internalEndDate As NotesDateTime
Dim counter As Integer
If startDate.LSLocalTime > endDate.LSLocalTime Then
'the dates are in the "wrong" order and need to be swapped
internalStartDate = New NotesDateTime(endDate.LSLocalTime)
internalEndDate = New NotesDateTime(startDate.LSLocalTime)
Else
internalStartDate = New NotesDateTime(startDate.LSLocalTime)
internalEndDate = New NotesDateTime(endDate.LSLocalTime)
End If
If ignoreTimes Then
Call internalStartDate.SetAnyTime
Call internalEndDate.SetAnyTime
End If
counter = 0
Do While internalStartDate.LSLocalTime <= internalEndDate.LSLocalTime
Call internalStartDate.AdjustYear(1)
counter = counter + 1
Loop
Call internalStartDate.AdjustYear(-1)
componentsList("years") = counter - 1
counter = 0
Do while internalStartDate.LSLocalTime <= internalEndDate.LSLocalTime
Call intenalStartDate.AdjustMonth(1)
counter = counter + 1
Loop
Call internalStartDate.AdjustMonth(-1)
componentsList("months") = counter - 1
Do while internalStartDate.LSLocalTime <= internalEndDate.LSLocalTime
Call intenalStartDate.AdjustDay(1)
counter = counter + 1
Loop
Call internalStartDate.AdjustDay(-1)
componentsList("days") = counter - 1
DateDifferenceYMD = componentsList
End Function
Again, it’s not necessarily the fastest way to go about it, but it takes away the burden of dealing with leap years, time zones and DST in your code and places that squarely on the product objects, with the benefit of beingreally, really easy to see what’s going on and reason about it.
As for the summation, you’ll need to handle that little matter of having the data you need buried in strings (you seem to have gotten that far already), then treat the years months and days as if they were places in a numering system - you know, do carryings. The problem is, as I mentioned earlier, that you’ll need to be arbitrary with the days-in-a-month thing. If you’re willing to call 30 days a month for summation purposes, you’re good.
Let’s say you have managed to arrive at a set of values for days, months and years. Doing the carries is relatively simple:
carryMonths := @Integer(days / 30);
days := days - (30 * carryMonths);
months := months + carryMonths;
carryYears = @Integer(months / 12);
months = months - (12 * carryYears);
years := years + carryYears;
Since these are durations rather than dates, though, there is no way to determine which months you’re dealing with, so you can’t get any finer than that - you don’t know how many days were in each of those months in the total, even if the individual durations are correct to the second.
Subject: the same hints from Karl apply
Also sum the total days, not the year, month, days. Then convert to years months days