You need to sign in to do that
Don't have an account?

Work Week Number Within a Year Date Calculation
Someone asked for help on figuring out a formula to calculate what work week a date falls within, with work weeks starting on Mondays, and I thought I'd share.
((CloseDate - DATE(YEAR(CloseDate), 1, 1) + (CASE( MOD( DATE( YEAR( CloseDate ), 01, 01 ) - DATE(1900, 1, 7), 7),
0, - 0,
6, - 1,
2, + 2,
4, + 4,
5, + 5,
1, + 1,
+ 3))) / 7) + IF(MOD((CloseDate - DATE(YEAR(CloseDate), 1, 1) + (CASE( MOD( DATE( YEAR( CloseDate ), 01, 01 ) - DATE(1900, 1, 7), 7),
0, - 0,
6, - 1,
2, + 2,
4, + 4,
5, + 5,
1, + 1,
+ 3))), 7) = 0, 0, 1)
Just change CloseDate to the date field in question.
what would you have to edit to get this to work for the start date being 03/072011 (3rd of july 2001) is it possible?
Thanks
J
Here is my solution that works in case anyone else was interested, works great :)
MOD(FLOOR((Last_Modified_Date__c - DATE(YEAR(Last_Modified_Date__c),1,1))/7) + 26,52)+1
I tried your formula is not working right, try this one seems more accurate:
(MOD(FLOOR((DateValue(Execution_Time__c)-7-DATEVALUE("2007-01-01"))/7),52))+1
This worked wonderfully! Thank you for sharing.
your_date_field__c" with the date field you are testing.