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:

Copy to Clipboard

Alright, so here is the full formula for week number:

Copy to Clipboard
Salesforce week number formula date/time field

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:

Copy to Clipboard
Salesforce week number formula date field

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!

0 0 vote
Article Rating