Hi,
I would like to calculate the calendar week number for a given date?
seems not as simple as counting the number of days since January 1st as it depends on the week day too.
thx!
Hi,
I would like to calculate the calendar week number for a given date?
seems not as simple as counting the number of days since January 1st as it depends on the week day too.
thx!
if you are using 2007 use the =weeknum function
If you do not see the function in earlier versions choose-tools-add-ins-select the top two and ok
so easy with the right function installed...
thx a lot
What constitutes Week #1. This year, January 1 fell on Thursday.
Is week1 Jan 1 - Jan Jan 3rd (assuming Sun - Sat week)
or Jan 1 - Jan 7 (Thursday - Wednesday)
or Jan 4 - Jan 10 (first full week)?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
I don't know Chemist B. I supposed there should be an "official" definition of calendar weeks since many do work on week numbers.
Looking in a calendar (and at the result of Weeknum) it looks like week one can be as short as just Sunday. There will also be a week 53... which probably is week 1 of the following year.
Using WEEKNUM week 1 always starts on 1st Jan and week 2 starts on the following Sunday [Monday if you use WEEKNUM(date,2)], so, yes, by that definition some weeks have only 1 day (and some years, i.e. leap years beginning on a Saturday, have a week 54).
In Europe using "ISO week numbers", all weeks have 7 days but week 1 may start in the previous year, it always starts on the first Monday on or after 29th December.
and here is the formula if weeknum id not available (french function names though), A1 the date for which to find the week number:
=ENT((A1-DATE(ANNEE(A1);1;1)+JOURSEM(DATE(ANNEE(A1);1;1))-1)/7)+1
ENT should be INT
ANNEE is YEAR
JOURSEM should be WEEKDAY
This formula will also replicate WEEKNUM
=INT((A1-DATE(YEAR(A1),1,1)-WEEKDAY(A1))/7)+2
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks