Forum Discussion

nlaauw's avatar
nlaauw
Regular Visitor
4 years ago
Solved

Calendar dates aligning to weeks

Help! I have people attend training and need to analyse their attendance dates by date but also by term and week which align to school terms and weeks. The spreadsheet with the data only has the date, so I need to align dates to a school week as follows:

4.10.21, 5.10.21, 6.10.21, 7.10.21, 8.1021 is Term 4 Week 1

11.1.0.21, 12.10.21, 13.10.21, 14.10.21, 15.10.21 Term 4 Week 2

18.10.21, 19.10.21, 20.10.21, 21.10.21, 22.10.21 Term 4 Week 3 

This continues until 17.12.21 which is Term 4 Week 11

 

Is there any way to set this up?

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi nlaauw ,

     

    Please try the following formula:

     

    Table = 
VAR StartDate =
    DATE ( 2021, 10, 4 )
VAR EndDate =
    DATE ( 2021, 12, 17 )
VAR S_WeekNum =
    WEEKNUM ( StartDate, 2 ) - 1
VAR DateRange =
    FILTER (
        CALENDAR ( StartDate, EndDate ),
        NOT WEEKDAY ( [Date], 2 ) IN { 6, 7 }
    )
RETURN
    ADDCOLUMNS (
        DateRange,
        "Week",
            "Week "
                & WEEKNUM ( [Date], 2 ) - S_WeekNum,
        "Term", 4
    )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • KNP's avatar
    KNP
    Super User
    4 years ago

    The easiest way would be to create another table/spreadsheet that has dates and week numbers in it.

    You could then merge this table with your data to include the week numbers.

     

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    Hi nlaauw ,

     

    Please try the following formula:

     

    Table = 
VAR StartDate =
    DATE ( 2021, 10, 4 )
VAR EndDate =
    DATE ( 2021, 12, 17 )
VAR S_WeekNum =
    WEEKNUM ( StartDate, 2 ) - 1
VAR DateRange =
    FILTER (
        CALENDAR ( StartDate, EndDate ),
        NOT WEEKDAY ( [Date], 2 ) IN { 6, 7 }
    )
RETURN
    ADDCOLUMNS (
        DateRange,
        "Week",
            "Week "
                & WEEKNUM ( [Date], 2 ) - S_WeekNum,
        "Term", 4
    )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.