How to find weeks between on the given two weeks

system · December 5, 2008, 5:36am

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

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

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

Hi All,

Got the final solution,

Many thanks for NewyorkBaasha

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

system · December 8, 2008, 5:54pm

You are welcome

KhoushikTTT (BOB member since 2005-02-24)

system · December 8, 2008, 6:29pm

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

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

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)