Applies To: SharePoint Online and SharePoint On-Premises
Description:
This article describes the steps to use ‘Calculated Columns’ to create SharePoint Views for filtering the items of a list based upon a Date/Time column in the same list.
The ‘Date/Time’ column used for reference in this article is titled ‘ClosedDate’.
These steps can also be implemented using the default Date/Time columns in a SharePoint list like ‘Created’ and ’Modified’ or any other custom Date/Time type of a column(s).
Detailed Steps:
Use Case 1:
Creating a filter in a SharePoint View to display items closed in the ‘Current Calendar Month’:
1. Navigate to the desired list -> List Settings -> Create Column -> Calculated -> Configure as shown below:
Column Display Name: Start Of Current Month
Column Internal Name: StartOfCurrentMonth
This column is used to show the first day of the month based on ‘ClosedDate’ column value, and the formula used is:
=DATE(YEAR(ClosedDate),MONTH(ClosedDate),1)
2. If required, uncheck the ‘Add to default view’ so that this column remains hidden from the main list and click ‘OK’ to save the configuration for this column.
3. Similarly, create another ‘Calculated’ column and configure it as shown below:
Column Display Name: End Of Current Month
Column Internal Name: EndOfCurrentMonth
This column is used to show the last day of the month based on ‘ClosedDate’ column value, and the formula used is:
=DATE(YEAR(ClosedDate),MONTH(ClosedDate)+1,1)-1
4. For this column also, if required, uncheck the ‘Add to default view’ option and click on ‘OK’ to save the column in the list.
5. Navigate back to the list and from the list ribbon, select ‘All Items’ -> Create new view as shown below:
6. Provide a name to this view. In this example, we have named it as ‘Tickets Closed In Current Month’ to display only those items in the list that are closed in the current month as shown below:
Filter conditions for this view are used as:
7. Save the above view and on selecting this view, the items in the list will be displayed as shown below:
Use Case 2:
Creating a filter in a SharePoint View to display items closed in the ‘Previous Calendar Month’:
1. Create three ‘Calculated’ columns in the list as explained in the ‘Use Case 1’ and name them as ‘Closed Year’, ‘Closed Month’ and ‘Is Previous Month’ respectively. The names can be changed as per the requirement.
2. The ‘Closed Year’ column can be configured as shown below:
Column Display Name: Closed Year
Column Internal Name: ClosedYear
This column is used to calculate the year on which the Ticket was closed based on the date entered in ‘ClosedDate’ column, and the formula used is:
=YEAR(ClosedDate)
3. The ‘Closed Month’ calculated column can be configured as shown below:
Column Display Name: Closed Month
Column Internal Name: ClosedMonth
The purpose of this column is to calculate the closed month based upon the value in the ‘ClosedDate’ column, and the formula used is:
=MONTH(ClosedDate)
4. The ‘Is Previous Month’ column can be configured as shown below:
Column Display Name: Is Previous Month
Column Internal Name: IsPreviousMonth
The formula used above is:
=IF(AND(YEAR(TODAY())=YEAR(DATE(YEAR(ClosedDate),MONTH(ClosedDate)+1,0)),MONTH(ClosedDate)=MONTH(TODAY())-1),”Yes”,”No”)
Example:
If ClosedDate is 2023-05-15 and Today is 2023-06-13, the formula will evaluate:
YEAR(TODAY()) = 2023
YEAR(DATE(YEAR(2023),MONTH(2023-05-15)+1,0)) = 2023
MONTH(2023-05-15) = 5
MONTH(2023-06-13)-1 = 5
The conditions are true, so the result will be “Yes”.
If ClosedDate is 2023-04-15 and Today is 2023-06-13, the conditions will not be met, so the result will be “No”.
Explanation of the formula:
- TODAY(): This function returns the current date.
- YEAR(TODAY()): This extracts the year from the current date.
- MONTH(TODAY()): This extracts the month from the current date.
- YEAR(ClosedDate): This extracts the year from the ‘ClosedDate’ column.
- MONTH(ClosedDate): This extracts the month from the ClosedDate.
- DATE(YEAR(ClosedDate), MONTH(ClosedDate) + 1, 0): This constructs a date by taking the year of ‘ClosedDate’, the month of ‘ClosedDate’ plus one, and day zero of that month.
- The day zero of the next month actually gives the last day of the current month. This is a used to get the last day of the month for the ‘ClosedDate’ column.
- YEAR(DATE(YEAR(ClosedDate), MONTH(ClosedDate) + 1, 0)): This extracts the year from the date calculated above, which is the last day of the month of the ‘ClosedDate’ column.
- MONTH(TODAY()) – 1: This calculates the previous month from the current date.
5. Create a new view titled ‘Tickets Closed Last Month’ as explained in #5 of Use Case 1. It can be configured as shown below:
The filter conditions used for this view are:
6. Once the above configured view is saved, the output from the list displaying the items will be as shown below:
Use Case 3:
Creating a filter in a SharePoint View to display items closed in the ‘Current Week of the Calendar Month’:
- Create 2 calculated columns as explained in the above Use Case 1 titled ‘Start of Current Week’ and ‘End of Current Week’ respectively.
- The configuration for ‘Start of Current Week’ calculated column is as shown below:
Column Display Name: Start of Current Week
Column Internal Name: StartofCurrentWeek
This column is added to calculate the start date of the week based on the value from the ‘ClosedDate’ column, and the formula used is:
=ClosedDate-WEEKDAY(ClosedDate)+1
Note: The start date for the week will depend upon the Site’s Regional Settings as shown below:
3. Similarly, ‘End of Current Week’ calculated column can be configured as shown below:
Column Display Name: End of Current Week
Column Internal Name: EndOfCurrentWeek
The purpose of this column is to calculate the end date for the week and, the formula used for this column is:
=[Start of Week]+6
4. As per the previous ‘Use Cases’ explained above, create a SharePoint View titled ‘Tickets Closed in Current Week’ to display only those items in a list that were closed in the current week of the current Calendar month as shown below:
The filter conditions used for this view are:
5. As per the above configured view, the output will be as shown below:
Use Case 4:
Creating a filter in a SharePoint View to display the items closed in the ‘Previous Week’
- Create a Calculated Column titled ‘Is Previous Week’ and configure it as shown below:
Column Display Name: Is Previous Week
Column Internal Name: IsPreviousWeek
The formula used is:
=IF(
AND(
[ClosedDate] >= (TODAY() – WEEKDAY(TODAY(), 1) – 7),
[ClosedDate] <= (TODAY() – WEEKDAY(TODAY(), 1) – 1)
),
“Yes”,
“No”
)
Example Calculation:
Let us assume that the current date is Wednesday, June 12, 2024 and it is required to find if a ClosedDate falls in the previous week (from Sunday, June 2, 2024, to Saturday, June 8, 2024).
Today’s Date:
TODAY() = June 12, 2024
Day of the Week:
WEEKDAY(TODAY(), 1) = 4 (Wednesday)
Previous Sunday:
TODAY() – WEEKDAY(TODAY(), 1) – 7
June 12, 2024 – 4 – 7
June 12, 2024 – 11
June 1, 2024
Previous Saturday:
TODAY() – WEEKDAY(TODAY(), 1) – 1
June 12, 2024 – 4 – 1
June 12, 2024 – 5
June 7, 2024
Explanation of the formula:
TODAY(): Gets the current date.
WEEKDAY(TODAY(), 1): Returns the day of the week for the current date, with 1 representing Sunday and 7 representing Saturday.
TODAY() – WEEKDAY(TODAY(), 1) – 7: Calculates the start date of the previous week (Sunday).
TODAY() – WEEKDAY(TODAY(), 1) – 1: Calculates the end date of the previous week (Saturday).
AND([ClosedDate] >= (TODAY() – WEEKDAY(TODAY(), 1) – 7), [ClosedDate] <= (TODAY() – WEEKDAY(TODAY(), 1) – 1)): Checks if the ‘ClosedDate’ falls within the previous week.
IF(…, “Yes”, “No”): Returns “Yes” if the ‘ClosedDate’ is within the previous week, otherwise returns “No”.
2. Create a SharePoint View titled ‘Tickets Closed Last Week’ as shown below:
The filter conditions used for this view are:
3. On selecting this view, the output from the list will be shown as below:
Use Case 5:
Creating a filter in a SharePoint View to display items closed in the ‘Previous Year’:
- To display the items of the list that were closed last year, we need to create one Calculated Columns as ‘Is Previous Year’.
- The configuration for this column can be done as shown:
Column Display Name: IsPreviousYear
Column Internal Name: IsPreviousYear
The formula used in this column is:
=IF(YEAR([ClosedDate]) = YEAR(TODAY()) – 1, “Yes”, “No”)
Explanation of the above formula:
- YEAR([ClosedDate]): Extracts the year from the ClosedDate column.
- YEAR(TODAY()) – 1: Calculates the previous year based on the current year.
- IF(YEAR([ClosedDate]) = YEAR(TODAY()) – 1, “Yes”, “No”): Checks if the year of the ClosedDate is equal to the previous year. If it is, it returns “Yes”; otherwise, it returns “No”.
3. Create a SharePoint view titled as ‘Tickets Closed Last Year’ as shown below:
The filter conditions used for this view are:
On selecting the above configured view, the output from the list will be as shown below:
Use Case 6:
Creating a filter in a SharePoint view to display all the Tickets closed in the ‘Current Year’:
1. Create a new ‘Calculated’ column titled ‘Is Current Year’ and configure it as shown below:
Column Display Name: Is Current Year
Column Internal Name: IsCurrentYear
The formula used in the above screenshot is:
=IF(YEAR(ClosedDate)=YEAR(TODAY()),”Yes”,”No”)
Explanation of the above formula:
- YEAR([ClosedDate]): Extracts the year from the ClosedDate column.
- YEAR(TODAY()): Extracts the current year from today’s date.
- IF(YEAR([ClosedDate]) = YEAR(TODAY()), “Yes”, “No”): Checks if the year of the ClosedDate is equal to the current year. If it is, the formula returns “Yes”; otherwise, it returns “No”.
2. Once the above column has been created, create a new SharePoint List View and configure it as shown below:
The filter conditions used for this view are:
3. From this configured view, the output will be as shown below: