system
December 5, 2008, 5:36am
#1
Hi All,

I have two objects. One is “project start week” and “project end week”. This two object format is yyyywk (ex: 200830). So i have to find between these two weeks difference according to each project.

Ex: “PST” is project name, this “project start week” is: 200752, “project end week” is 200804. Then i have to find, how many weeks b/w these two weeks.

I used 200752-200804 (project start week- project end week), but it was showing wrong count (52), but it should show 5weeks…But other situation
it was working fine.(200830-200823=7)

Iam using BOXIR2, MY SQL2000.

Please any one help me. ( universe level or report level any where)

srinu.bos (BOB member since 2007-06-06)

system
December 5, 2008, 8:33am
#2
At report level,

```
=If(toNumber(left([EndWeek];4))>toNumber(left([StartWeek];4));((toNumber(right([EndWeek];2))+52*((toNumber(left([EndWeek];4))-toNumber(left([StartWeek];4)))))-toNumber(right([StartWeek];2)));
If(toNumber(left([StartWeek];4))>toNumber(left([EndWeek;4));((toNumber(right([EndWeek;2))+52*((toNumber(left([StartWeek];4))-toNumber(left([EndWeek;4)))))-toNumber(right([StartWeek];2)));
(toNumber(right([EndWeek;2))-toNumber(right([StartWeek];2))))
```

The second IF statement if true will yield negative number which will not occur (Assuming that start date wont occur end date)

KhoushikTTT (BOB member since 2005-02-24)

system
December 8, 2008, 12:34pm
#3
Thanks for ur replay,

This formula is working but some situvation is giving wrong count.

Note: some times my startdate>enddate and enddate>startdate and startdate=enddate.

I think Both the conditions are not satisfied at time.
EX:200905>200851 and 200852>200902 and 200845=200845

Please help me…

srinu.bos (BOB member since 2007-06-06)

system
December 8, 2008, 1:10pm
#4
Hi All,

Got the final solution,

Many thanks for NewyorkBaasha

srinu.bos (BOB member since 2007-06-06)

system
December 8, 2008, 5:54pm
#5
You are welcome

KhoushikTTT (BOB member since 2005-02-24)

system
December 8, 2008, 6:29pm
#6
Could you post the formula which worked for you so that it amy help members in future.

KhoushikTTT (BOB member since 2005-02-24)

system
December 10, 2008, 4:47am
#7
sure

=(ToNumber(Right(FormatNumber([MThis Week Forecast];"######");2))+52*((ToNumber(Left(FormatNumber([MThis Week Forecast];"######");4))-ToNumber(Left([MPlanned Completion Week No];4)))))-ToNumber(Right([MPlanned Completion Week No];2))

But in future may be this will give wrong count because we are taking 52weeks only in the formula, if its leap year then has 53weeks in leap year. so what is the solution for this situation?

Plese help me on this problem also

srinu.bos (BOB member since 2007-06-06)

system
December 10, 2008, 6:31pm
#8
Here the formula to include leap year,

`=If (Mod(ToNumber(Left(FormatNumber([MThis Week Forecast];"######");4));4)>0);(ToNumber(Right(FormatNumber([MThis Week Forecast];"######");2))+52*((ToNumber(Left(FormatNumber([MThis Week Forecast];"######");4))-ToNumber(Left([MPlanned Completion Week No];4)))))-ToNumber(Right([MPlanned Completion Week No];2));(ToNumber(Right(FormatNumber([MThis Week Forecast];"######");2))+53*((ToNumber(Left(FormatNumber([MThis Week Forecast];"######");4))-ToNumber(Left([MPlanned Completion Week No];4)))))-ToNumber(Right([MPlanned Completion Week No];2))) `

KhoushikTTT (BOB member since 2005-02-24)