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:
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!