• Sherry Culbertson 1
• NEWBIE
• 0 Points
• Member since 2015

• Chatter
Feed
• 0
• 0
• 1
Likes Given
• 0
Questions
• 1
Replies

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.
Message Edited by Buell on 08-20-2009 04:39 PM
Message Edited by Buell on 08-20-2009 04:43 PM
• August 19, 2009
• Like
• 1

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.
Message Edited by Buell on 08-20-2009 04:39 PM
Message Edited by Buell on 08-20-2009 04:43 PM
• August 19, 2009
• Like
• 1