Convert seconds to years, months, days

[Former Member]

Post Author: bilbo

CA Forum: Formula

I have a total number of seconds from a formula between two dates, how do I go about converting this to 'x years, y months z days'?

Any help/pointers greatly appreciated!

Bill

[Former Member]

Post Author: bilbo

CA Forum: Formula

That works great!

Many thanks!

Bill

[Former Member]

Post Author: SKodidine

CA Forum: Formula

numbervar y := 31556926; // seconds in a year

numbervar m := 2629743.83; // seconds in a month

numbervar d := 86400; // seconds in a day

//

numbervar years := truncate(a/y);

numbervar months := if remainder(a,y) < m then 0 else truncate(remainder(a,y)/m);

numbervar days := if months < 1 then remainder(a,y)/d else truncate((remainder(remainder(a,y),m))/d);

//

totext(years,0) & ' Years ' & totext(months,0) & 'Months ' & totext(days,0) & 'days';

where A is the calculated average in seconds.

[Former Member]

Post Author: bilbo

CA Forum: Formula

Thanks for the reply.

I have start and end dates in the database but I need to calculate an average so I'm using datediff ("s",{table.startdate},{table.enddate}) for each record.

I can then add up the total number of seconds in all records then divide by the number of entries to give me an average in seconds. This is the figure I need to convert to YY,MM,DD

I have found a formula in the KB to convert seconds to hh:mm:ss and I've butchered it to try and do what I want. It seems to give me an answer but I'm not confident it is correct as my skills with Crystal formulas are very very limited! What I have is done is shown below:

local numbervar RemainingSeconds;local numbervar Yr ;local numbervar Mh;local numbervar Dy;

Yr := truncate({@divL} / 31556926);RemainingSeconds := {@divL} - (Yr *31556926);

Mh := truncate(RemainingSeconds/2629743);Dy := {@divL} - (Yr * 31556926) -(Mh * 2629743);

totext(Yr,"0") + " years " + totext(Mh,"0") +" months"// + totext(Dy,"00")

In the last section the days I have rem'd out as if I leave them in I get "1 years 10 months 850996 days" - Hence I think my formula is fricked up somewhere!

Again, all help and assistance welcomed!

Bill

[Former Member]

Post Author: SKodidine

CA Forum: Formula

Bilbo of the shire, are you calculating the difference between the two dates in seconds or is it coming from a database? If you have the two dates then here is a formula to calculate the difference in years, months & days.

http://technicalsupport.businessobjects.com/cs/forums/post/6084.aspx