date calculation

[rezdragon]

ok here is something i was wondering im sure this may be easy but is there a way for me to specify how many weeks aprt something is ... ie

i have a global field that uses the date(10/1/Year) and then i have a field that says Date ( Month ; Day ; Year ) is there a way for me to get the weeks in number difference from the global to the current date but not change over to the next current date till the global field passes 10/01/2007

[comment]

The difference, in weeks, between two dates is:

( date2 - date 1 ) / 7

I'm afraid the rest went over my head.

[rezdragon]

ok i got it here is what i did and hopefully it may help someone else

for the beginning of the fiscal year i did

Case (WeekOfYear ( GET MODIFIED YEAR );1;1 )

get modified year being 10/01/year

for current date i did

WeekOfYear ( GET CURRENT YEAR )

get current year being Date(month;day;year)

then to give me a 2 digit week i did

"0" &""& If ( WeekOfYear ( GET MODIFIED YEAR )=1 ; WeekOfYear ( Date ( Month - 9 ; Day ; Year ) ) ; WeekOfYear ( Date ( Month - 9 ; Day ; Year ) ) )

its a little weird but we publish weekly and i needed to get the exact issue number starting from a specific date that is the same each year.

im sure there were much better ways of doing this but heck if it works it works... hehe

the reason i forced it to see the beginning date of the fiscal year to be 1/1/year was because i ran into a problem on jan 1st were when the new year rolled over i got a negative number so i forced 10/1/year to be seen as the 1st of the year cause the year roll over to not be a problem

Edited November 16, 2006 by Guest