Forum Discussion
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!
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 DateFormatDynamic 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 ) )
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:
- Thanks!
