Forum Discussion

blomjo's avatar
blomjo
Copper Contributor
Jul 12, 2023

Calculate date based on current years week number and specific week day

Hi,

 

Is there any way to, in a sharepoint list, calculate a date based on 2 columns containing (1)current years week number and (2)a specifik week day.

 

Current year=2023

Column1: 28
Column2: Tis (Tuesday)

The calculated date for current year would be 11072023 (DDMMYYY).

 

This is for a planning tool, presented in powerapps, where  a great deal of the events repeats every year. In order for me to highlight events in future I need a date. I was able to do it in PowerApps (calculate date based on said columns) but I have problem using/reaching that date more than presenting it, if I had that date in a column from the data source I thought it would be easier.

Calculated Column
date
JSON
SharePoint
Sharepoint Powerapps
  • H2O's avatar
    H2O
    Iron Contributor
    Jul 12, 2023
    You has a SharePoint list that contains two columns: one with the current year's week number and one with a specific weekday. You wants to calculate a date based on these two columns. For example, if the week number is 28 and the weekday is Tuesday, the calculated date for 2023 would be 11/07/2023 (DD/MM/YYYY).

    A calculated column in SharePoint is a way to create a formula that can use data from other columns or functions to calculate a value. You can use various functions and operators in your formula, such as mathematical, logical, text, date and time, etc. You can also specify the data type and format of your calculated column, such as number, currency, date and time, etc.

    To calculate a date based on the week number and weekday, you can use a formula like this:

    ```
    =DATE(YEAR(TODAY()),1,-2)-WEEKDAY(DATE(YEAR(TODAY()),1,3))+[Week Number]*7+[Week Day]
    ```

    This formula assumes that the week number is based on ISO 8601 standard, which means that the first week of the year is the week that contains the first Thursday of the year, and that the weekdays are numbered from 1 (Monday) to 7 (Sunday). The formula also assumes that you have a column named [Week Number] that contains the week number, and a column named [Week Day] that contains the weekday number. You can change these names to match your columns.

    The formula works by first calculating the date of the first Monday of the year using this expression:

    ```
    =DATE(YEAR(TODAY()),1,-2)-WEEKDAY(DATE(YEAR(TODAY()),1,3))
    ```

    Then it adds the number of weeks and days to get the desired date using this expression:

    ```
    +[Week Number]*7+[Week Day]
    ```

    You can enter this formula in your calculated column settings and set the data type to Date and Time. You can also choose how to display the date format, such as Standard, Friendly, ISO 8601, etc.

Resources