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

  • Chatter
    Feed
  • 0
    Best Answers
  • 0
    Likes Received
  • 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