"rounding up" calculated expressions

[ianw111]

I have got an expression to calculate the number of weeks between the charging start date and the dispatch date - wanting the system to round up the answer to the full week eg if start 1/Jan and dispatch 14/Jan then system correctly returns 2 weeks, if dispatch date is 15/Jan then I want it to return 3 weeks but it still returns 2 weeks and same right up to say 20/Jan difference. Is there any simple round up function similar to excel, search has proved fruitless.

No of weeks: DateDiff("ww",[charging period start],[dispatch date])

Any help appreciated thanks IW -- Cleveland. UK

 

[Fear Naught]

Why don't you try calculating the number of days first and through a division of 7 you can calculate the weeks. If the result is not an integer you can then just add 1 to the number of weeks.

Without spending some time on this to workout some code (and today I do not have that time to spare - sorry) I would hazard a guess that the MOD Operator might help you out using something like

if [number of days] Mod 7 = 0 then
  [number of weeks] = [number of days] / 7
else
  [number of weeks] = ([number of days]/ 7) + 1
end if

HTH