Number of weeks and days between two dates.

[deemat]

Hello, I'm trying to calculate the number of weeks between two dates with a remainder of days left. For instance 10-01-11 to 10-18-11 should be 2 weeks and 4 days. I am using the following to get the number of weeks. But I am having trouble getting the it to show remaining days. If there are remaining days, it just puts it into a complete week. DateDiff("w",StartDate,EndDate)

Thanks,

 

[infinitizon]

This should work

numbervar tsecs := DateDiff("s",DateValue(2011,09,01),DateValue(2011,09,30)); //Get the seconds value first
numbervar nweeks := truncate(tsecs/604800); // divide by the seconds in a week
tsecs := remainder(tsecs,604800); // find the left over seconds
numbervar ndays := truncate(tsecs/86400); // divide by the seconds in a day

//You can go on to get the other parts
//tsecs := remainder(tsecs,86400); // find the left over seconds
//numbervar nhours := truncate(tsecs/3600); // divide by the seconds in an hour
//tsecs := remainder(tsecs,3600); // find the left over seconds
//numbervar nmin := truncate(tsecs/60); // divide by the seconds in a minute
//tsecs := remainder(tsecs,60); // find the left over seconds

// now that we have all the components, we put it together in a sentence
totext(nweeks,0)+" weeks, "+totext(ndays,0)+" days, "//... + totext(nmin,0)+" mins, "+totext(tsecs,0)+" sec."


____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.

 

[infinitizon]

You can put your own startdate and end date as appropriate.
I used those periods for my test

 

[deemat]

Thanks for your help. I believe this will work. The only problem I am encountering is that the field I am using is a date field but I get the error "date-time is required here" because it is formatted as 9/17/2011 wereas in your formula the dates are formatted as 2011,09,01. Is there any way to fix this error?

 

[mmjb]

How are you passing your date to your formula? If using parameters you could try something like this, if using a formula, replace the parameter fields with formula fields.

// get number of weeks between two dates
// get number of days between two dates
// set string variable weeks and days
// ?StartDate is a parameter field = 10/1/2011
// ?EndDate is a parameter field = 10/18/2011
numbervar Weeks := DateDiff("w",{?StartDate},{?EndDate});
numbervar Days := DateDiff("d",{?StartDate},{?EndDate});
stringvar DateDifference := totext(weeks,0,"") & " weeks and " & totext((days - (weeks*7)),0,"") & " days";

This would return "2 weeks and 3 days".

 

[infinitizon]

That should not be an issue, just change to
DateValue('9/17/2011'), or DateValue('09-17-2011') where necessary.
CR can take string values of date in the DateValue() function.

You can also pass parameters if necessary.