Hi Jesper!
In any week number problem of whatever nature it is crucial to
establish your definitions of:
· What day of the week a week starts on, and
· How is the first day of the first week determined.
For a discussion on Week numbers generally see:
Chip Pearson:
http://www.cpearson.com/excel/weeknum.htm
The major difficulty is alternative algorithms for determining the
first day of week one and for determining what day a week starts on.
Here are various systems and / requirements that all produce their own
solutions.
1. The Analysis ToolPak WEEKNUM function allows two bases. Both
provide for Jan-1 as being the first day of week number 1 with the two
alternatives providing that Sunday (Alternative 1) or Monday
(Alternative 2) being the first day of all subsequent weeks.
2. We can also use a simple formula for calculating week numbers
where Jan-1 is defined as the first day of week one and all subsequent
weeks starting 7 days later.
3. There are formulas and functions for calculating week numbers
where the ISO8601:2000 algorithm is used. Monday is regarded as the
first day of the week with week 1 starting on the Monday of the week
containing Jan-4.
4. Financial Year based systems where the financial year might
start on a given date.
5. Special requirements such as fixed weekly cycles (e.g. 4
weeks, 13 weeks etc). Such requirements need to specify the base date
and what day the week starts.
6. Term / Semester week numbering. Here we have "gross" systems
that include mid-term / mid-semester breaks and "net" systems which
exclude those weeks.
7. Month week numbering systems where the 1st of the month is
the first day of the first week and the fifth week is just an odd days
week.
8. Month week numbering systems where the first week starts on
the first Monday or Sunday of a Month and overlaps the subsequent
month.
9. Other bases limited only by the ingenuity of people who
devise systems (perhaps without considering whether the above
alternatives might be easier for all concerned).
Once we have you week numbering basis we can then set to work to find
the Monday of the week number you have.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Hi MVP's And Sesons Greatings to All !
Need som help on this problem!
I have i UserForm in wich i enter á weeknumber, e.g.
current weeknumber. What i need is excel to return the
date of monday in the specified week, in a field in my
UserForm, in order to use the date for further actions.
Now, i don't want to use a cell to perform the
calculation, but strictly use the VBA code to handle the
matter.
Any suggestions?....Well I thank you all in advance, and
wish you all a merry christmas and a happy new year !*S*
Best to U All.. Jesper- Denmark
