Solved

How to get the number of week by program

Posted on 2012-03-13
3
268 Views
Last Modified: 2012-03-28
Dear all experts,

I'm looking for a function which is used to get the number of week for specific date, for example.

If I enter 14 Mar 2012 in this function, it will return week 3
If I enter  6 Apr 2012 in this function, it will return week 2

This script will be deployed to an Access or Excel application.  Please kindly advise, thanks.
0
Comment
Question by:towo2002
3 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 37715824
0
 
LVL 3

Expert Comment

by:DaFranker
ID: 37715850
The following page explaining week numbers in Excel VBA might prove helpful: Week Numbers In Excel

Otherwise, you could use parsing using the Day() and Weekday() functions, counting how many days from day 1 of the month to the entered day match the "start of a week".
0
 
LVL 8

Accepted Solution

by:
Wiesje earned 300 total points
ID: 37719554
Week numbers are a complicated matter - but only because there are so many different ways to calculate the beginning of the first week.

For a previous question I found the following site: http://www.cpearson.com/excel/WeekNumbers.aspx

If the most standard week numbers is what you require you should use the solution from that question.

That is implementing the following function:
Public Function IsoWeekNumber(InDate As Date) As Long
    IsoWeekNumber = DatePart("ww", InDate, vbMonday, vbFirstFourDays)
End Function

And using it in your code as follows:
IsoWeekNumber(aDate)

Where aDate is a of course a Date type.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I comeā€¦
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question