Get EndofMonth with weekNum and Year only

[ilcaa]

i only have year and weeknumber in my table. How can I get the End of Month date?

thanks

 

[rollis13]

This is the best I could manage: non sure about international WeekNum. Example: in A1 the year and in B1 the WeekNum, in C1 try:
=EOMONTH(DATE($A1,1,1)+($B1-IF(WEEKDAY(DATE($A1,1,1),2)<5,1,0))*7-WEEKDAY(DATE($A1,1,1),2)+1,0)
even this will probably work:
=EOMONTH(DATE(A1,1,B1*7-2)-WEEKDAY(DATE(A1,1,3)),0)

 

[ilcaa]

This is the best I could manage: non sure about international WeekNum. Example: in A1 the year and in B1 the WeekNum, in C1 try:
=EOMONTH(DATE($A1,1,1)+($B1-IF(WEEKDAY(DATE($A1,1,1),2)<5,1,0))*7-WEEKDAY(DATE($A1,1,1),2)+1,0)
even this will probably work:
=EOMONTH(DATE(A1,1,B1*7-2)-WEEKDAY(DATE(A1,1,3)),0)

thanks. the 2nd formula works in Excel... I needed it for PQ, ill see if I can translate it with the PQ functions, if cant ill repost...

can you give me the logic of the 2nd formula. How does subtracting 3rd day in Jan make this work? or subtracting 2 ? whats the logic

 

[Kerryx]

Book1
	A	B	C	D
1	Week	year	Month	End of Month
2	42	2022	10	31/10/2022
3	2	2020	1	31/01/2020
4	3	2021	1	31/01/2021
5	4	2022	1	31/01/2022
6	5	2022	2	28/02/2022
7	6	2022	2	28/02/2022
8	7	2022	2	28/02/2022
9	8	2022	2	28/02/2022
10	9	2022	3	31/03/2022
Sheet1

Cell Formulas
Range	Formula
C2:C10	C2	=MONTH(DATE(B2,1,A2*7-2))
D2:D10	D2	=EOMONTH("01/"&C2&"/"&B2,0)

 

[rollis13]

I needed it for PQ

Sorry, I missed the detail .

 

[ilcaa]

Sorry, I missed the detail .

no worries, tried to replicate in PQ and I get error... can you see the wrror???

Date.EndOfMonth(#date(Date.Year([Q4Dates]), Date.Month([Q4Dates]), [weekNum]*7-1))
- Date.DayOfWeek( Date.Year([Q4Dates], 1, 3))

 

[Kerryx]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week", Int64.Type}, {"year", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "WeekStart", each Date.StartOfWeek(Date.AddWeeks(#date(
[year], 1, 1), [Week]-1), Day.Monday)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "EndofMonth", each Date.EndOfMonth([WeekStart]))
in
    #"Added Custom1"

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    fnEndOfMonth = (year as number, week as number) as date =>
        Date.EndOfMonth(List.Select(List.Transform({Number.From(#date(year,1,1)).. Number.From(#date(year,12,31))},
        (x)=> let df = Date.From(x) in {Date.WeekOfYear(df), df}), (y)=>  y{0}=week){0}{1}),
    tbl = Table.AddColumn(Source, "End of Month", each fnEndOfMonth([Year], [WeekNum]))
in
    tbl

Book1
	A	B	C	D	E	F	G
1	Year	WeekNum		Year	WeekNum	End of Month
2	2005	51		2005	51	12/31/2005
3	2003	28		2003	28	7/31/2003
4	2002	23		2002	23	6/30/2002
5	2005	24		2005	24	6/30/2005
6	2008	29		2008	29	7/31/2008
7	2021	44		2021	44	10/31/2021
8	2002	48		2002	48	11/30/2002
9	2009	23		2009	23	5/31/2009
10	2004	24		2004	24	6/30/2004
11	2020	51		2020	51	12/31/2020
12	2008	17		2008	17	4/30/2008
13	2017	50		2017	50	12/31/2017
14	1999	28		1999	28	7/31/1999
15	2013	44		2013	44	10/31/2013
16	2007	16		2007	16	4/30/2007
17	2017	11		2017	11	3/31/2017
18	1999	16		1999	16	4/30/1999
19
Sheet1

 

[ilcaa]

both work great,,, still working to understand functions.