## How to get week numbers from dates?

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-01`

and`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(...)`

.