How to get week numbers from dates?

excel week number of month from date
how to calculate week number
convert days to weeks in excel
number of weeks between two dates - excel
excel week start and end date
convert week number to date sql
get week number from date javascript
excel week number financial year

Looking for a function in R to convert dates into week numbers (of year) I went for week from package data.table. However, I observed some strange behaviour:

> week("2014-03-16") # Sun, expecting 11
[1] 11
> week("2014-03-17") # Mon, expecting 12
[1] 11
> week("2014-03-18") # Tue, expecting 12
[1] 12

Why is the week number switching to 12 on tuesday, instead of monday? What am I missing? (Timezone should be irrelevant as there are just dates?!)

Other suggestions for (base) R functions are appreciated as well.

Base package

Using the function strftime passing the argument %V to obtain the week of the year as decimal number (01–53) as defined in ISO 8601. (More details in the documentarion: ?strftime)

strftime(c("2014-03-16", "2014-03-17","2014-03-18", "2014-01-01"), format = "%V")

Output:

[1] "11" "12" "12" "01"

Excel formula: Get week number from date, To get the week number from the day from a date, you can use the WEEKNUM function. In the example shown, the formula in C5, copied down,� This page lists all weeks in 2020. There are 53 weeks in 2020. All weeks are starting on Monday and ending on Sunday. Please note that there are multiple systems for week numbering, this is the ISO week date standard (ISO-8601), other systems use weeks starting on Sunday (US) or Saturday (Islamic).

if you try with lubridate:

library(lubridate)
lubridate::week(ymd("2014-03-16", "2014-03-17","2014-03-18", '2014-01-01'))

[1] 11 11 12  1

The pattern is the same. Try isoweek

lubridate::isoweek(ymd("2014-03-16", "2014-03-17","2014-03-18", '2014-01-01'))
[1] 11 12 12  1

3 Tips for Calculating Week Numbers from Dates in Excel, #2 – Calculate the Number of Weeks Between Dates To determine how many weeks elapsed between two dates, we can use a simple formula to find the number of days between the dates, then divide by 7. The formula will return a decimal number. We can change the number format to a Fraction to display the number of days. Calculate the week number of any date and find the beginning and end date of a specific week of any year.

Actually, I think you may have discovered a bug in the week(...) function, or at least an error in the documentation. Hopefully someone will jump in and explain why I am wrong.

Looking at the code:

library(lubridate)
> week
function (x) 
yday(x)%/%7 + 1
<environment: namespace:lubridate>

The documentation states:

Weeks is the number of complete seven day periods that have occured between the date and January 1st, plus one.

But since Jan 1 is the first day of the year (not the zeroth), the first "week" will be a six day period. The code should (??) be

(yday(x)-1)%/%7 + 1

NB: You are using week(...) in the data.table package, which is the same code as lubridate::week except it coerces everything to integer rather than numeric for efficiency. So this function has the same problem (??).

How to convert week number to date or vice versa in Excel?, On the other hand, you can also apply the WEEKNUM function to convert a date to corresponding week number. 1. Select a blank cell you will return the week number, enter this formula: =WEEKNUM(B1,1), and press the Enter key. Week numbers in Excel How to get the week number from a date. To get the ISO week number (1-53) for a date in cell A1, use =ISOWEEKNUM(A1). The is supported in Excel 2013 and later, and Excel 2011 for Mac and later. To get the corresponding year, use =YEAR(A1 - WEEKDAY(A1, 2) + 4). Read more about ISOWEEKNUM() and WEEKDAY() in the Excel Help

I think the problem is that the week calculation somehow uses the first day of the year. I don't understand the internal mechanics, but you can see what I mean with this example:

library(data.table)

dd <- seq(as.IDate("2013-12-20"), as.IDate("2014-01-20"), 1)
# dd <- seq(as.IDate("2013-12-01"), as.IDate("2014-03-31"), 1)

dt <- data.table(i = 1:length(dd),
                 day = dd,
                 weekday = weekdays(dd),
                 day_rounded = round(dd, "weeks"))
## Now let's add the weekdays for the "rounded" date
dt[ , weekday_rounded := weekdays(day_rounded)]
## This seems to make internal sense with the "week" calculation
dt[ , weeknumber := week(day)]
dt 

    i        day   weekday day_rounded weekday_rounded weeknumber
1:  1 2013-12-20    Friday  2013-12-17         Tuesday         51
2:  2 2013-12-21  Saturday  2013-12-17         Tuesday         51
3:  3 2013-12-22    Sunday  2013-12-17         Tuesday         51
4:  4 2013-12-23    Monday  2013-12-24         Tuesday         52
5:  5 2013-12-24   Tuesday  2013-12-24         Tuesday         52
6:  6 2013-12-25 Wednesday  2013-12-24         Tuesday         52
7:  7 2013-12-26  Thursday  2013-12-24         Tuesday         52
8:  8 2013-12-27    Friday  2013-12-24         Tuesday         52
9:  9 2013-12-28  Saturday  2013-12-24         Tuesday         52
10: 10 2013-12-29    Sunday  2013-12-24         Tuesday         52
11: 11 2013-12-30    Monday  2013-12-31         Tuesday         53
12: 12 2013-12-31   Tuesday  2013-12-31         Tuesday         53
13: 13 2014-01-01 Wednesday  2014-01-01       Wednesday          1
14: 14 2014-01-02  Thursday  2014-01-01       Wednesday          1
15: 15 2014-01-03    Friday  2014-01-01       Wednesday          1
16: 16 2014-01-04  Saturday  2014-01-01       Wednesday          1
17: 17 2014-01-05    Sunday  2014-01-01       Wednesday          1
18: 18 2014-01-06    Monday  2014-01-01       Wednesday          1
19: 19 2014-01-07   Tuesday  2014-01-08       Wednesday          2
20: 20 2014-01-08 Wednesday  2014-01-08       Wednesday          2
21: 21 2014-01-09  Thursday  2014-01-08       Wednesday          2
22: 22 2014-01-10    Friday  2014-01-08       Wednesday          2
23: 23 2014-01-11  Saturday  2014-01-08       Wednesday          2
24: 24 2014-01-12    Sunday  2014-01-08       Wednesday          2
25: 25 2014-01-13    Monday  2014-01-08       Wednesday          2
26: 26 2014-01-14   Tuesday  2014-01-15       Wednesday          3
27: 27 2014-01-15 Wednesday  2014-01-15       Wednesday          3
28: 28 2014-01-16  Thursday  2014-01-15       Wednesday          3
29: 29 2014-01-17    Friday  2014-01-15       Wednesday          3
30: 30 2014-01-18  Saturday  2014-01-15       Wednesday          3
31: 31 2014-01-19    Sunday  2014-01-15       Wednesday          3
32: 32 2014-01-20    Monday  2014-01-15       Wednesday          3
     i        day   weekday day_rounded weekday_rounded weeknumber

My workaround is this function: https://github.com/geneorama/geneorama/blob/master/R/round_weeks.R

round_weeks <- function(x){
    require(data.table)
    dt <- data.table(i = 1:length(x),
                     day = x,
                     weekday = weekdays(x))
    offset <- data.table(weekday = c('Sunday', 'Monday', 'Tuesday', 'Wednesday', 
                                     'Thursday', 'Friday', 'Saturday'),
                         offset = -(0:6))
    dt <- merge(dt, offset, by="weekday")
    dt[ , day_adj := day + offset]
    setkey(dt, i)
    return(dt[ , day_adj])
}

Of course, you can easily change the offset to make Monday first or whatever. The best way to do this would be to add an offset to the offset... but I haven't done that yet.

I provided a link to my simple geneorama package, but please don't rely on it too much because it's likely to change and not very documented.

Excel Formula, Just enter a date and the formula returns the week number of that date, from a specified Duration: 6:13 Posted: Oct 29, 2018 To get the week number from the day from a date, you can use the WEEKNUM function. In the example shown, the formula in C5, copied down, is: =WEEKNUM(B5) With the date January 5, 2016 in B5, WEEKNUM returns 2 as the the week number.

if you want to get the week number with the year use: "%Y-W%V":

e.g    yearAndweeks <- strftime(dates, format = "%Y-W%V")

so

> strftime(c("2014-03-16", "2014-03-17","2014-03-18", "2014-01-01"), format = "%Y-W%V")

becomes:

[1] "2014-W11" "2014-W12" "2014-W12" "2014-W01"

Week Number Calculator: What Week of the Year Is , Calculate the week number of any date and find the beginning and end date of a specific week of any year. Convert week number to date with formulas. Supposing I have a random year and week number which are 2015 and 15 in a worksheet as following screenshot shown, and now I want to find out the specific dates from Monday to Sunday by this given week number. To calculate the date range by the specific week number, you can use the following formulas: 1.

Excel WEEKNUM function – convert week number to date and vice , Pseudocode: int julian = getDayOfYear(myDate) // Jan 1 = 1, Jan 2 = 2, etc int dow = getDayOfWeek(myDate) // Sun = 0, Mon = 1, etc int dowJan1� For January 2, you want it to return a 2 for the entire week, while for the week starting January 9, you want it to return a 9 for the inclusive dates. To achieve this, use Excel’s DAY() function. The approach will be to get the day associated to the specific date and deduct the weekday shown earlier: Cell D6 = DAY(B6-WEEKDAY(B6,3))

How do I calculate the week number given a date?, How to get the week number from a date. To get the ISO week number (1-53) for a date in cell A1 , use =ISOWEEKNUM( A1 )� For example, January 1st will always be in week #1. (Except in one special case, which we’ll mention below). And December 31st will always be #52. Dates in the middle will be assigned their own corresponding week numbers. Let’s take a look at the syntax before we get started:

Week numbers in Excel, This post will guide you how to convert a week number to a date with a formula in Excel. How do I calculate a date from a week number and a� Get week number from dates in T-SQL. It is best to use the following: select DATEPART(ISO_WEEK, getDate()) As when you have a year with a week 53 as in the case of 2015 it give unreliable results.

Comments
  • Try format(as.Date("2014-03-16"), "%U") or format(as.Date("2014-03-16"), "%W")
  • @GSee thanks, but that returns 11 instead 12 for the following: format(as.Date("2014-03-17"), "%U") and format(as.Date("2014-03-17"), "%W") !?
  • so, convert to integer and add 1. See ?strptime
  • That's what I am doing right now, actually. I was just wondering, why I have to make this workaround? I would expect the week to begin on monday (EU) or sunday (US), but not on tuesday?
  • Possible duplicate of as.Date produces unexpected result in a sequence of week-based dates
  • 2014-01-01and 2014-12-29 will get both 01.
  • @giordano That is correct as defined in ISO 8601. If the week (starting on Monday) containing 1 January has four or more days in the new year, then it is considered week 1. You can double-check this in any of the iso 8601 week calculators online.
  • ?week (lubridate) states: Weeks is the number of complete seven day periods that have occured between the date and January 1st, plus one.
  • @ChristianBorck isoweek is what you need?
  • That looks good, but my lubridate (v 1.3.1) package seems to be missing the isoweek function? Which version do you use?
  • @ChristianBorck I'm running lubridate_1.3.3 update it.
  • This is dangerous: strftime(c(as.Date("2014-01-01"),as.Date("2014-12-29")), format = "%Y-W%V") gives [1] "2014-W01" "2014-W01".
  • I always try to solve problems with base R first. So, I am with you. But your answer misses to get the (calendar) week number I am looking for!? (dt2$yday-1)%/%7 +1 for example only works right, if January 1st was a monday.
  • @ChristianBorck - Not to confuse things even further, but "right" depends on your definition of "week". The ISO-8601 standard defines a week to start on a Monday, although the week numbering depends on what day Jan 1 falls on. The week(...) function does not claim to use this standard. My point was that week(...) does not a appear to adhere to it's own definition. If you want ISO-8601 weeks (a good practice, by the way), use isoweek(...).