How to Calculate the Week Number Given a Date

How do I calculate the week number given a date?

Pseudocode:

int julian = getDayOfYear(myDate)  // Jan 1 = 1, Jan 2 = 2, etc...
int dow = getDayOfWeek(myDate)     // Sun = 0, Mon = 1, etc...
int dowJan1 = getDayOfWeek("1/1/" + thisYear)   // find out first of year's day
// int badWeekNum = (julian / 7) + 1  // Get our week# (wrong!  Don't use this)
int weekNum = ((julian + 6) / 7)   // probably better.  CHECK THIS LINE. (See comments.)
if (dow < dowJan1)                 // adjust for being after Saturday of week #1
    ++weekNum;
return (weekNum)

To clarify, this algorithm assumes you number your weeks like this:

S  M  T  W  R  F  S
            1  2  3    <-- week #1
4  5  6  7  8  9 10    <-- week #2
[etc.]

getDayOfWeek() and getDayOfYear() are standard date-object operations in most languages. If yours doesn't have them, you can count-forward from some known date (Jan 1, 1970 is a common one), after looking up to see what day of the week it was.

If you're going to implement your own date counting routines, remember that years that are divisible by 100 are NOT leap years, unless they are also divisible by 400. So 1900 was not a leap year, but 2000 was. If you're going to work far back in time, you have to mess with Gregorian vs Julian calendars, etc., see Wikipedia for loads of info on that.

This link talks about date/time functions in Windows/C++ in greater detail.

How can I calculate/find the week-number of a given date?

var currentCulture = CultureInfo.CurrentCulture;
var weekNo = currentCulture.Calendar.GetWeekOfYear(
                new DateTime(2013, 12, 31),
                currentCulture.DateTimeFormat.CalendarWeekRule,
                currentCulture.DateTimeFormat.FirstDayOfWeek);

Be aware that this is not ISO 8601 compatible. In Sweden we use ISO 8601 week numbers but even though the culture is set to "sv-SE", CalendarWeekRule is FirstFourDayWeek, and FirstDayOfWeek is Monday the weekNo variable will be set to 53 instead of the correct 1 in the above code.

I have only tried this with Swedish settings but I'm pretty sure that all countries (Austria, Germany, Switzerland and more) using ISO 8601 week numbers will be affected by this problem.

Peter van Ooijen and Shawn Steele has different solutions to this problem.

Here's a compact solution

private static int WeekOfYearISO8601(DateTime date)
{
    var day = (int)CultureInfo.CurrentCulture.Calendar.GetDayOfWeek(date);
    return CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(date.AddDays(4 - (day == 0 ? 7 : day)), CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);
}

It's been tested for the following dates

var datesAndISO8601Weeks = new Dictionary<DateTime, int>
                        {
                            {new DateTime(2000, 12, 31), 52},
                            {new DateTime(2001, 1, 1), 1},
                            {new DateTime(2005, 1, 1), 53},
                            {new DateTime(2007, 12, 31), 1},
                            {new DateTime(2008, 12, 29), 1},
                            {new DateTime(2010, 1, 3), 53},
                            {new DateTime(2011, 12, 31), 52},
                            {new DateTime(2012, 1, 1), 52},
                            {new DateTime(2013, 1, 2), 1},
                            {new DateTime(2013, 12, 31), 1},
                        };

foreach (var dateWeek in datesAndISO8601Weeks)
{
    Debug.Assert(WeekOfYearISO8601(dateWeek.Key) == dateWeek.Value, dateWeek.Key.ToShortDateString() + " should be week number " + dateWeek.Value + " but was " + WeekOfYearISO8601(dateWeek.Key));
}

How is the week number calculated based on a date?

How are these numbers derived?

Well, that depends on what system you're using, but I suspect you're after the ISO-8601 week number, which is defined like this:

2.2.10 calendar week number
ordinal number which identifies a calendar week within its calendar year according to the rule that the first calendar week of a year is that one which includes the first Thursday of that year and that the last calendar week of a calendar year is the week immediately preceding the first calendar week of the next calendar year

Bear in mind that a week in ISO-8601 starts on Monday and finishes on Sunday - so another way of expressing the "first Thursday" rule is that the first week of the year is the first week containing at least four days of the new year.

Another vital point is that when you're expressing values using "week of week year", you need to use the "week year" itself as well, not the normal year. So for Sunday January 3rd 2010, you might express this as "Week 53, week year 2009, day-of-week Sunday". It's all too easy to use the "wrong type of year" and end up messing up values around the end of December and start of January.

Week number based on Date

Assuming 30-07-18 is located @ A1. in B1, type in :

=WEEKNUM(A1,2)-WEEKNUM($A$1,2)+1

and drag it down. Glad that you'd tried. ( :

All the best.

Get the correct week number of a given date

As noted in this MSDN page there is a slight difference between ISO8601 week and .Net week numbering.

You can refer to this article in MSDN Blog for a better explanation: "ISO 8601 Week of Year format in Microsoft .Net"

Simply put, .Net allow weeks to be split across years while the ISO standard does not.
In the article there is also a simple function to get the correct ISO 8601 week number for the last week of the year.

Update The following method actually returns 1 for 2012-12-31 which is correct in ISO 8601 (e.g. Germany).

// This presumes that weeks start with Monday.
// Week 1 is the 1st week of the year with a Thursday in it.
public static int GetIso8601WeekOfYear(DateTime time)
{
    // Seriously cheat.  If its Monday, Tuesday or Wednesday, then it'll 
    // be the same week# as whatever Thursday, Friday or Saturday are,
    // and we always get those right
    DayOfWeek day = CultureInfo.InvariantCulture.Calendar.GetDayOfWeek(time);
    if (day >= DayOfWeek.Monday && day <= DayOfWeek.Wednesday)
    {
        time = time.AddDays(3);
    }

    // Return the week of our adjusted day
    return CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(time, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);
}

Calculate Week Numbers based on the initial given date to end date

Seems your looking for custom week definition rather that built-ins. But not overly difficult. The first thing is to convert from strings to dates (if columns actually coming off table this conversion is not required), and from there let Oracle do all the calculations as you can apply arithmetic operations to dates, except adding 2 dates. Oracle will automatically handle differing number of days per month correctly.
Two methods for this request:

Use a recursive CTE (with)

with dates(start_date,end_date) as 
     ( select date '2020-08-24' start_date
            , date '2020-12-31' end_date 
         from dual
     )
   , weeks (wk, wk_start, wk_end, e_date) as
     ( select 1, start_date, start_date+6 ld, end_date from dates
       union all 
       select wk+1, wk_end+1, wk_end+7, e_date
         from weeks 
        where wk_end<e_date
     )
select wk, wk_start, wk_end from weeks;

Use Oracle connect by

with dates(start_date,end_date) as 
     ( select date '2020-08-24' start_date
            , date '2020-12-31' end_date 
         from dual
     )
 select level wk
      , start_date+7*(level-1) wk_start
      , start_date+6+7*(level-1)
   from dates
 connect by level <= ceil( (end_date-start_date)/7.0);

Depend on how strict you need to be with the end date specified you may need to adjust the last row returned. Both queries do not make adjust for that. They just ensure no week begins after that date. But the last week contains the full 7 days, which may end after the specified end date.

Calculating the week number of a year in Excel

Based on @ScottCraner comment, I was able to get the result. Here is my excel formula.

=IF(AND(A1>=DATE(YEAR(A1),1,1),A1<DATE(YEAR(A1),1,8)-WEEKDAY(DATE(YEAR(A1),1,6))),53,WEEKNUM(A1,2))