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

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 ) )

PQ_DatesWeeks.xlsx33 KB

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

Forum Discussion

Create week number start with 1st July

Resources