Need help in month wise week formula.

[Vikas_Salvi]

Aug-24
	Week - 1	Week - 2	Week - 3	Week - 4	Week - 5
	1 - 4 Aug 24	5 - 11 Aug 24	12 - 18 Aug 24	19 - 24 Aug 24	25 - 31 Aug 24

If change the month the date must change by the week, week start from Monday to Sunday in excel.
want formula in excel.

 

[pecoflyer]

Hi and welcome
is this a request or an order? Using "please" and "Thanks" is customary.

 

[AlanSidman]

If you have your data as in column A, then with Power Query, you can have result as in Columns C & D.

as in attached.

Mcode from Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    #"Inserted Week of Month" = Table.AddColumn(#"Changed Type", "Week of Month", each Date.WeekOfMonth([Column1]), Int64.Type)
in
    #"Inserted Week of Month"

 

[Vikas_Salvi]

If you have your data as in column A, then with Power Query, you can have result as in Columns C & D.

as in attached.

Mcode from Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    #"Inserted Week of Month" = Table.AddColumn(#"Changed Type", "Week of Month", each Date.WeekOfMonth([Column1]), Int64.Type)
in
    #"Inserted Week of Month"

Thanks you Very Much,

I have attach the Excel file, need help in the formula for weekly, week start from Monday to Saturday, pls check its once.

 

[AliGW]

In B3 copied across:

=IF(B2="Week - 1",TEXT($A$1+0,"dd")&" - "&TEXT($A$1+WEEKDAY(A1+0,2)-1,"dd mmm yy"),TEXT($A$1+WEEKDAY($A$1+0,2)+((COLUMNS($B1:B1)-1)*7)-7,"dd")&" - "&TEXT(MIN(EOMONTH($A$1+0,0),$A$1+WEEKDAY($A$1+0,2)+(((COLUMNS($B1:B1)-1)*7)-1)),"dd mmm yy"))

 

[Vikas_Salvi]

In B3 copied across:

=IF(B2="Week - 1",TEXT($A$1+0,"dd")&" - "&TEXT($A$1+WEEKDAY(A1+0,2)-1,"dd mmm yy"),TEXT($A$1+WEEKDAY($A$1+0,2)+((COLUMNS($B1:B1)-1)*7)-7,"dd")&" - "&TEXT(MIN(EOMONTH($A$1+0,0),$A$1+WEEKDAY($A$1+0,2)+(((COLUMNS($B1:B1)-1)*7)-1)),"dd mmm yy"))

Thankyou Very Much Madam,

When changing the month the week day are not get from Monday to Sunday.

 

[AliGW]

Try this:

=IF(B2="Week - 1",IF(WEEKDAY($A$1+0,2)<>7,TEXT($A$1+0,"dd")&" - "&TEXT($A$1+7-WEEKDAY($A$1+0,2),"dd mmm yy"),TEXT($A$1+7-WEEKDAY($A$1+0,2),"dd mmm yy")),TEXT($A$1+7-WEEKDAY($A$1+0,2)+1+((COLUMNS($B1:B1)-1)*7)-7,"dd")&" - "&TEXT(MIN(EOMONTH($A$1+0,0),$A$1+WEEKDAY($A$1+0,2)+(((COLUMNS($B1:B1)-1)*7)-1)),"dd mmm yy"))

 

[djc]

Your use of a text string in cell A1 ('Aug-24) could potentially be causing problems, depending on your locale system settings. On my system, for example, =DATEVALUE(A1) returns August 24, 2024, when another system might return August 1, 2024. You'd be better off entering an actual date for the first of the month in cell A1 (e.g. 08/01/2024 or 01/08/2024), then apply custom cell formatting mmm-yy if desired.

Having said that, if you have Excel for MS365, you could also try something along these lines:

=LET(
    last, EOMONTH(A1, 0),
    first, DATE(YEAR(last), MONTH(last), 1),
    calendar, SEQUENCE(6, 7, first - WEEKDAY(first, 2) + 1),
    BYCOL(TRANSPOSE(calendar), LAMBDA(c,
        IF(TAKE(c, 1) > last, "", TEXT(MAX(first, TAKE(c, 1)), "dd - ") & TEXT(MIN(last, TAKE(c, -1)), "dd mmm yy")))
    )
)

BTW, monthly calendars should contain 6 weeks, not 5 (e.g. September and December 2024 will spill into 6 weeks when treating Monday as the first day of the week). I hope that helps!

 

[p45cal]

At first, I didn't respond to this question because all I came up with seemed convoluted and long while I felt sure there'd be a simple, elegant and robust formula for what is a simplish request. Now that others have responded without such a neat formula here's my offering, quite similar to @djc's suggestion but using WEEKNUM:

=LET(Dte,A1,First,DATE(YEAR(Dte),MONTH(Dte),1),calendar,SEQUENCE(,DAY(EOMONTH(First,0)),First),weeknums,WEEKNUM(calendar,2),BYCOL(UNIQUE(weeknums,TRUE),LAMBDA(a,LET(ss,IF(a=weeknums,calendar),TEXT(MIN(ss),"d") & TEXT(MAX(ss)," - d mmm yy")))))

Unfortunately, an examination of @Vikas_Salvi 's attachment shows he is using Excel 2010. I'm too lazy to try and produce a formula for that version.
@AliGW , try your formula on a different month, eg Sep 24 or Dec 24, I'm getting periods of about 2 weeks:

 

[AliGW]

It was working when I tested it. Never mind!

 

[AliGW]

Here's a version of djc's that does the whole lot with no blank cells in the spill array:

=LET(
    l, EOMONTH(A1, 0),
    f, DATE(YEAR(l), MONTH(l), 1),
    d, SEQUENCE(6, 7, f - WEEKDAY(f, 2) + 1),
    w, "Week - "& SEQUENCE(, 6),
    b, BYCOL(TRANSPOSE(d), LAMBDA(c,
        IF(TAKE(c, 1) > l, "", TEXT(MAX(f, TAKE(c, 1)), "dd - ") & TEXT(MIN(l, TAKE(c, -1)), "dd mmm yy")))
    ),
    v, VSTACK(w,b),
FILTER(v,b<>"")
)

 

[djc]

If a legacy variant is needed for older versions of Excel, I would recommend defining a few variables in Name Manager (Ctrl+F3) first:

StartOfMonth:
=DATE(YEAR(Sheet1!$A$1),MONTH(Sheet1!$A$1),1)

EndOfMonth:
=DATE(YEAR(Sheet1!$A$1),MONTH(Sheet1!$A$1)+1,0)

EndOfWeek0:
=StartOfMonth-WEEKDAY(StartOfMonth,2)

Then, use the following formula in cell B3 and copy it across:

=IF(EndOfWeek0+(RIGHT(B2)-1)*7+1 > EndOfMonth, "",
TEXT(MAX(StartOfMonth, EndOfWeek0+(RIGHT(B2)-1)*7+1), "dd - ")&
TEXT(MIN(EndOfMonth, EndOfWeek0+RIGHT(B2)*7), "dd mmm yy"))

See attached, if needed...

 

[Vikas_Salvi]

If a legacy variant is needed for older versions of Excel, I would recommend defining a few variables in Name Manager (Ctrl+F3) first:

StartOfMonth:
=DATE(YEAR(Sheet1!$A$1),MONTH(Sheet1!$A$1),1)

EndOfMonth:
=DATE(YEAR(Sheet1!$A$1),MONTH(Sheet1!$A$1)+1,0)

EndOfWeek0:
=StartOfMonth-WEEKDAY(StartOfMonth,2)

Then, use the following formula in cell B3 and copy it across:

=IF(EndOfWeek0+(RIGHT(B2)-1)*7+1 > EndOfMonth, "",
TEXT(MAX(StartOfMonth, EndOfWeek0+(RIGHT(B2)-1)*7+1), "dd - ")&
TEXT(MIN(EndOfMonth, EndOfWeek0+RIGHT(B2)*7), "dd mmm yy"))

View attachment 87993

See attached, if needed...

Thankyou very much madam...