How to create a current week view in Microsoft Lists

By João Ferreira Nov 19, 2021 Formatting, Microsoft Lists 11 Comments

Creating views in Microsoft Lists is a straightforward process and it can be done using the UI to apply filters, group elements or even order the items as you wish. Despite the simplicity you can go a step further and use expressions and functions to display your data in different formats and create advanced views.

In this article you will learn how to create a view to filter the items created in the current week from Monday to Sunday.

To implement this solution, you’ll need to use calculated columns and functions, if you are not familiar with the concept you can learn more about it here.

How to create week columns in Microsoft Lists

The first step is to create 3 new columns:

• Week start – The initial day of week when the item was created
• Week end – The final day of the week when the item was created
• Week day – This one is option and will be used to easily show the day of the week when the item was created.

To create the new columns, you will need to do the following process 3 times one for each column:

1. On your lists in Microsoft Lists click in the Add Column option and then in the drop down select More… At the time of writing calculated columns are only available in the classic SharePoint interface.
   
2. Provide a name for the column
3. In the type of column select Calculated (calculation based on other columns)
4. The next step is defining the formula that will calculate the dates based in the created column that exists by default in the list. The list below has the formulas and the return data type you should select for each one of the 3 columns. This calculation uses the WEEKDAY function and you can learn more about it here.
   • Week start
     • Formula: =Created-(WEEKDAY(Created,3))
     • Data Type: Date and Time with Date Only format
   • Week end
     • Formula: =Created+(6-WEEKDAY(Created,3))
     • Data Type: Date and Time with Date Only format
   • Week day
     • Formula: =WEEKDAY(Created,3)
     • Data Type: Number

   

5. Click OK to add the column to the view

Once you complete this process for each one of the columns you should get something like what is illustrated in the following picture with the 3 new columns added to the end of the lists.

How to create a list view for the current week

Now that you got all the columns needed added to the list it is time to create the view that will filter the items that were created in the current week, to implement it you should do the following:

1. From the list in Microsoft Lists click in the cog icon and then click in the List Settings option
   
2. Scroll down to the bottom of the page and click Create View
   
3. From the list of available options click on Standard View
4. Provide a name for the view
5. Select the columns you would like to display
6. Expand the Filter section
7. Select the option Show items only when the following is true
8. Build the condition as shown in the following image

   Week Start is lee than or equal to [Today] AND Week End is greater than or equal to [Today]

   

   The [Today] syntax is used to filter a column based on the current day

9. Click OK to save it

Your newly created view should now filter all the items created in the current week, to start using it you just need to choose it from the view selector dropdown.

As you can see the in the above picture the new view is not showing the beginning and the end of the week as that information is only needed to apply the filter. It is showing the weekday when it was created, but with a number and not as string.

How to format weekdays using column formatting in Microsoft Lists

The WEEKDAY function used in the weekday calculated column returns the day of the week between 0 and 6, despite the unfriendly format you will be able to modify it using Microsoft Lists column formatting by doing the following:

1. With the new view selected click in the dropdown icon in the weekday column
2. Expand the Column settings
3. Click in the Format this column option
   
4. From the format column side panel click in the Advanced mode
5. In the text box paste the following JSON code

{
   "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
   "elmType": "div",
   "txtContent": "=if(@currentField == 0, 'Monday', if(@currentField == 1, 'Tuesday', if(@currentField == 2, 'Wednesday', if(@currentField == 3, 'Thursday', if(@currentField == 4, 'Friday', if(@currentField == 5, 'Saturday', if(@currentField == 6, 'Sunday','')))))))"
}

6. Click Save

With the current week view created and the weekday column formatted you will get a result similar to what is shown in the following image.

If you would like to implement this solution for a different date range or to start your week on a Sunday let me know in the comment section and I’ll help you out building the formulas and the formatting for that.