Forum Discussion

Imi_Jay's avatar
Imi_Jay
Brass Contributor
Apr 27, 2022
Solved

Create week number start with 1st July

Hi ,
I am using Excel 365 , Power Query.

I want to create a column that shows week starting from 1/7/2022 to 31/6/2023 where week 1 start from 1/7/2022.

How can I do it?

Thanks!

BI & Data Analysis
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 28, 2022

    Imi_Jay 

    To play with variants

    Power Query

    let
    Start = Number.From(#date(2022,7,1)),
    End = Number.From(#date(2023,6,30)),
    Date = Table.FromList({Start..End}, Splitter.SplitByNothing(), {"Date"} ),
    Week = Table.AddColumn(
        Date,
        "Week #",
        each "Week " & Text.From(Number.IntegerDivide([Date] - Start, 7) +1),
        type text),
    DateFormat = Table.TransformColumnTypes(Week,{{"Date", type date}})
in
    DateFormat

    Dynamic array

    =LET(
   start,   DATE(2022,7,1),
   end,    DATE(2023,6,30),
   date,   SEQUENCE( end-start+1,,start),
   week, "Week " & INT((date-start)/7 ) + 1,
 IF({1,0}, date, week ) )

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 28, 2022

    Imi_Jay 

    To play with variants

    Power Query

    let
    Start = Number.From(#date(2022,7,1)),
    End = Number.From(#date(2023,6,30)),
    Date = Table.FromList({Start..End}, Splitter.SplitByNothing(), {"Date"} ),
    Week = Table.AddColumn(
        Date,
        "Week #",
        each "Week " & Text.From(Number.IntegerDivide([Date] - Start, 7) +1),
        type text),
    DateFormat = Table.TransformColumnTypes(Week,{{"Date", type date}})
in
    DateFormat

    Dynamic array

    =LET(
   start,   DATE(2022,7,1),
   end,    DATE(2023,6,30),
   date,   SEQUENCE( end-start+1,,start),
   week, "Week " & INT((date-start)/7 ) + 1,
 IF({1,0}, date, week ) )
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Apr 28, 2022

    Hi Imi_Jay 

     

    (31/6/2023 doesn't exist, adjusted to 30/6/2023 below). Not sure where/how you want to add that column so the following might not be exactly what you want... (Edited)

    let
    StartDate = #date(2022,7,1),
    EndDate = #date(2023,6,30),
    Days = Duration.Days(EndDate - StartDate) +1,
    Weeks = Number.RoundUp(Days / 7),
    Table = Table.FromColumns(
        {
            List.Dates(StartDate, Days, #duration(1,0,0,0)),
            List.FirstN(
                List.TransformMany(List.Numbers(1, Weeks), each
                    List.Numbers(1, 7), (w,d)=> "Week " & Text.From(w)
                ),
                Days
            )
        },
        type table [Date=date, Fiscal Week=text]
    )
in
    Table

     

     

    This creates a Table of 2 columns:

Resources