Hey everyone! Today I’ll show the formula for the week number based on a date field. This became an issue for me since I had to do reports that were broken down by calendar weeks. So when I was grouping dates by calendar week the grouping would look like this:
10/18/2020-10/24/2020
10/25/2020-10/31/2020
…
It is not visually appealing when you are viewing the entire year.
One year has approximately 52 weeks. So if the date field says 10/29/2020 – our formula field should say it’s week 44. That is because week 44 is from Monday, October 26, 2020 until (and including) Sunday, November 1, 2020.
So to get started let’s create a number formula field with no decimals. As an example, we’ll use the Created Date field. Since Created Date is a Date/Time field we’ll have to convert it into a date field. In Salesforce, it’s very easy to do by using DATEVALUE function. So in our case, it’ll look like this:
Alright, so here is the full formula for week number:
If you would like to get the current week number from a date field, we’ll have to take out date/time to date conversion:
Now you can use this field in your reports & dashboards. When you group by Week Number field, you’ll be able to sort your data in a sequence that you like. Your reports and dashboards will look much better.
Please let me know if you have any questions in the comment section below. See you next time!
2022 Update:
Salesforce has now added a function for this. You can simply use the ISOWEEK-function which returns the ISO 8601-week number (from 1 through 53) for the given date, ensuring that the first week starts on a Monday.
ISOWEEK( CreatedDate )
Thanks Oleksandr, I’ve been keen for a much tidier visualisation of this for a while. Trying to figure out how to adapt it to numbering the weeks based on the Fiscal year. Can we just do this with a conditional offset? At a basic level if your financial year started in February, then our real week 1 would be scored week 5, maybe week 6. Let’s say it is week 5, then subtracting four would get us there, but of course subtracting four off of week 1 would get us nonsense with a negative week. So an if statement to… Read more »
Hi Alex! I hope you were able to figure out the best approach. I had to do offset on my end, just to make sure company’s schedule match Salesforce. I can’t think of any other approach.
Thanks oleksandr, could you please help me on : if i want to filter the data for cases created within last 10 weeks ?
Hi – I found this formula to be very helpful. Thank you! I have a trend report where I count the number of cases opened per week rolling last 20 weeks. Using your formula, the report sorted fine until I hit week 11. Now week 2 is sorted after week 17. I need a way to make the week number default to two digits zero+week number. I thought of adding…If LEN week =1 then concatenate 0 to week value…forcing the the two digit week number…. no joy. Actual sort order: 2021 Week 52, 2022 Week 10, 2022 Week 11…2022 Week… Read more »
GOT IT TO WORK … TEXT(YEAR(DATEVALUE(CREATED_DATE))) & ” WEEK “ & IF(LEN(TEXT(CEILING((( DATEVALUE(CREATED_DATE) – DATE(YEAR(DATEVALUE(CREATED_DATE)), 1, 1) + 1) +
MOD(DATE(YEAR(DATEVALUE(CREATED_DATE)), 1, 1) – DATE(1900, 1, 7), 7)) / 7)))=1,“0”&TEXT(CEILING((( DATEVALUE(CREATED_DATE) – DATE(YEAR(DATEVALUE(CREATED_DATE)), 1, 1) + 1) +
MOD(DATE(YEAR(DATEVALUE(CREATED_DATE)), 1, 1) – DATE(1900, 1, 7), 7)) / 7)),TEXT(CEILING((( DATEVALUE(CREATED_DATE) – DATE(YEAR(DATEVALUE(CREATED_DATE)), 1, 1) + 1) +
MOD(DATE(YEAR(DATEVALUE(CREATED_DATE)), 1, 1) – DATE(1900, 1, 7), 7)) / 7)))
Salesforce has now added a function for this. You can simply use the ISOWEEK-function which returns the ISO 8601-week number (from 1 through 53) for the given date, ensuring that the first week starts on a Monday.
Thank you – you just saved me a major headache!!!
I know this is an older post but running into an issue with my current field now that I am in a 53 week year and wondering if you have advice…. it is returning hte wrong week associated with the date field….MOD(FLOOR( ( Scheduled_Construction_Date__c – DATE(2019,01,01)-6)/7),52)+2
Hi Kerry! I’m glad you were able to find an answer in the comment section. You can use new ISOWEEK() formula that has been added recently.
For the old approach, if I were to get the wrong week number, I’d just simply add 1 or substract 1 from the returned value to get the right number. But this has to be maintained every year.