1

I had a nice formula that was counting the week of the year, but as we approach the end of year it has stopped working. It now shows "1" in December. Sample versions:

    CEILING((( Date__c - DATE(YEAR( Date__c ), 1, 1) + 1) +
MOD(DATE(YEAR( Date__c ), 1, 1) - DATE(1900, 1, 7), 7)) / 7)

also getting 1 back is:

FLOOR( (
Date__c - DATE( YEAR( Date__c),1,1) +
MOD( DATE(YEAR(Date__c),1,1) - DATE(1900, 1, 7), 7)
) / 7)
+ 1

Does anyone know a formula that is working now? thanks Related: this formula for Current Week of the year is off, too, showing 3 on Jan 4th of the year:

MOD(FLOOR( ( TODAY() -DATEVALUE("2006-01-01" ))/7),52)+1
2

The SFDC Common Date Formulas Doc uses this formula

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

You can find the current week by determining how many days there have been in the current year and dividing that value by 7. The IF() statement ensures that the week number the formula returns doesn’t exceed 52. So if the given date is December 31 of the given year, the formula returns 52, even though it’s more than 52 weeks after the week of January.

5
  • this is a common problem with hand rolled Date processing, because there is 52.14 weeks in a year, and people just assume they can calculate date-weeks by dividing down the date themselves, and then in the last 4 days of the year it starts going wrong. There are a number of ways (including this) to deal with it, but the key thing is to remember it will affect you when you are coding!! you obvs get my point tho + happy Christmas Dec 25 '15 at 21:36
  • @SimonLawrence -- I'm just "a-quoting from the SFDC doc"
    – cropredy
    Dec 25 '15 at 22:28
  • ah yeah! by "get my point" I meant "get my vote" because what you posted was absolutely right!! sorry, it sounded like I was talking down at you :D Dec 25 '15 at 22:32
  • @simonLawrence lol - and enjoy the holidays
    – cropredy
    Dec 25 '15 at 22:38
  • Thanks. Relatedly, I have a calculator field for "Current Week" and right now on Jan 4th it's showing 3. I will add to the question for your reference and try the suggested method.
    – jaw999
    Jan 4 '16 at 14:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.