week number

E

ENGNMA

New Member
Joined
Jan 12, 2011
Messages
14
****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5Cuser%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> A1 is a date and i try to find week number i already use =Weeknum(A1,1) but this function give me a serial of month for all years WEEK (1,2,3,….51)
[FONT=&quot] But i am looking for function give for example in January week 1, 2,3,4,5 then start again from February 1,2,..etc[/FONT]
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
I'm probably over-complicating this but I went for a formula that allows for the week start / end to be on a specific day.

=WEEKNUM(A1)-WEEKNUM(EOMONTH(A1,-1)+1)+1

almost works, but not quite right, hopefully will give someone else an idea.
 
Upvote 0
jasonb75 said:
I'm probably over-complicating this but I went for a formula that allows for the week start / end to be on a specific day.

=WEEKNUM(A1)-WEEKNUM(EOMONTH(A1,-1)+1)+1

almost works, but not quite right, hopefully will give someone else an idea.
Click to expand...
Your formula works fine.
Why not?
 
Upvote 0
jasonb75 said:
almost works, but not quite right
Click to expand...

I suppose it depends on exactly what's required......

Andrew's formula assigns week 1 to the first 7 days of the month, week 2 to the next 7 etc. irrespective of day of the week

your version gives week 1 to the first of the month and then week 2 starts on the next Sunday. This formula will do that too

=INT((13-WEEKDAY(A1)+DAY(A1))/7)
 
Upvote 0
barry houdini said:
This formula will do that too

=INT((13-WEEKDAY(A1)+DAY(A1))/7)
Click to expand...

Thanks barry, I knew something on that line had to be possible, just couldn't figure it out.

As you say, it depends what is required so I'm leaving this now until we get feedback from ENGNMA, hopefully one of the current solutions will be correct.

My original theory was to run on the same principal as the WEEKNUM function, for example, based on sunday as first day of the week, week 1 of feb would start on jan 30.

Think it could be easier said than done though.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
D
  • Question Question
UserForm within Macro
Replies
1
Views
1K
DanteAmor
DanteAmor
C
  • Question Question
3 Vlookups from 2 Values
Replies
1
Views
706
AlanY
A
A
  • Question Question
Add Sequence numbers Based on Dates
Replies
1
Views
3K
Jeffrey Mahoney
Jeffrey Mahoney
Gerald Higgins
  • Question Question
VBA, make code move across from main file to extract file
Replies
9
Views
2K
Gerald Higgins
Gerald Higgins

Forum statistics

Threads
1,225,149
Messages
6,183,186
Members
453,151
Latest member
Lizamaison

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top