Skip to main content
Topics#Data ManagementRobert Gilmore's Question
Robert Gilmore (Sidewalk) asked in #Data Management
Oct 1, 2015, 5:36 PM

Need help writing a date formula that doesn't take up so many characters

I need this formula to tell me what week number a specific date is, but it's approaching the 5,000 char limit. Any suggestions on how I can re-write it to get the same "Week x" but not have to spell out every single date? : 

 

CASE(Summary_Date_Field__c, 

 

DATE(2015,08,01), "Week 1", 

 

DATE(2015,08,02), "Week 1", 

 

DATE(2015,08,03), "Week 1", 

 

DATE(2015,08,04), "Week 1", 

 

DATE(2015,08,05), "Week 1", 

 

DATE(2015,08,06), "Week 1", 

 

DATE(2015,08,07), "Week 1", 

 

DATE(2015,08,08), "Week 2", 

 

DATE(2015,08,09), "Week 2", 

 

DATE(2015,08,10), "Week 2", 

 

DATE(2015,08,11), "Week 2", 

 

DATE(2015,08,12), "Week 2", 

 

DATE(2015,08,13), "Week 2", 

 

DATE(2015,08,14), "Week 2", 

 

DATE(2015,08,15), "Week 3", 

 

DATE(2015,08,16), "Week 3", 

 

DATE(2015,08,17), "Week 3", 

 

DATE(2015,08,18), "Week 3", 

 

DATE(2015,08,19), "Week 3", 

 

DATE(2015,08,20), "Week 3", 

 

DATE(2015,08,21), "Week 3", 

 

DATE(2015,08,22), "Week 4", 

 

DATE(2015,08,23), "Week 4", 

 

DATE(2015,08,24), "Week 4", 

 

DATE(2015,08,25), "Week 4", 

 

DATE(2015,08,26), "Week 4", 

 

DATE(2015,08,27), "Week 4", 

 

DATE(2015,08,28), "Week 4", 

 

DATE(2015,08,29), "Week 5", 

 

DATE(2015,08,30), "Week 5", 

 

DATE(2015,08,31), "Week 5", 

 

DATE(2015,09,01), "Week 1", 

 

DATE(2015,09,02), "Week 1", 

 

DATE(2015,09,03), "Week 1", 

 

DATE(2015,09,04), "Week 1", 

 

DATE(2015,09,05), "Week 1", 

 

DATE(2015,09,06), "Week 1", 

 

DATE(2015,09,07), "Week 2", 

 

DATE(2015,09,08), "Week 2", 

 

DATE(2015,09,09), "Week 2", 

 

DATE(2015,09,10), "Week 2", 

 

DATE(2015,09,11), "Week 2", 

 

DATE(2015,09,12), "Week 2", 

 

DATE(2015,09,13), "Week 2", 

 

DATE(2015,09,14), "Week 3", 

 

DATE(2015,09,15), "Week 3", 

 

DATE(2015,09,16), "Week 3", 

 

DATE(2015,09,17), "Week 3", 

 

DATE(2015,09,18), "Week 3", 

 

DATE(2015,09,19), "Week 3", 

 

DATE(2015,09,20), "Week 3", 

 

DATE(2015,09,21), "Week 4", 

 

DATE(2015,09,22), "Week 4", 

 

DATE(2015,09,23), "Week 4", 

 

DATE(2015,09,24), "Week 4", 

 

DATE(2015,09,25), "Week 4", 

 

DATE(2015,09,26), "Week 4", 

 

DATE(2015,09,27), "Week 4", 

 

DATE(2015,09,28), "Week 5", 

 

DATE(2015,09,29), "Week 5", 

 

DATE(2015,09,30), "Week 5", 

 

"None")
11 answers
  1. Steve Molis (You Owe Me a Beer, Inc.)
    Oct 1, 2015, 6:03 PM
    Do you mean like this? 

     

    Datatype = Formula

     

    Result = Text 

     

    Formula = 

    "Week " + 
    TEXT(
    IF( 
      CEILING( ( date - DATE( YEAR( date ), 1, 1) + 1) / 7) > 52,
      52,
      CEILING( ( date - DATE( YEAR( date ), 1, 1) + 1) / 7)
    )
    )

     

     
Write an answer...
0/9000