This might be a easy question but I haven't been able to figure out how to get what I need.
I have a spreadsheet that people use to enter time card information that shows all the work dates in a year. What I need to do is to extract the week of the year each date belongs to and the corresponding year it belongs to for pay purposes.
I've used WEEKNUM(A2,21) to extract the date (because the week starts on a Monday) and that seems to have worked OK.
But when I go to extract the year, I am running into an issue. I can't use YEAR(A2) because that would show the calendar year which won't always reflect the pay year.
For example, take the following dates:
| Date | Desired Value (Week - Year) | Actual Value (Week - Year) |
|---|---|---|
| 12/27/2018 | 51-2018 | 51-2018 |
| 12/30/2018 | 51-2018 | 51-2018 |
| 12/31/2018 | 1-2019 | 1-2018 |
| 1/1/2017 | 52-2016 | 52-2017 |
12/31/2018 is technically the first work period in 2019 but using the Year formula on the dates gives me 2018 when it should be reflecting that it is the first time period for the year 2019. Similar scenario with a date like 1/1/2017.
How can I accomplish this?
Hi,
Sounds like you want the ISO Year.
Try:
=YEAR(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,1)-MOD((DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,1)-2),7)+(7*IF(MOD((DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,1)-2),7)>3,1,0))+7)
Replace A1 to your cell reference.
That seems to have worked. Thank you!
Would you mind explaining what it's doing, so I can understand it better?
It will take me a week to write about this 🙂
You will have to read a few articles: https://stackoverflow.com/questions/26528263/convert-year-and-iso-week-to-date-in-excel
The above formula works based on the ISO week date system, where the week starts on Monday and the week containing the 1st Thursday of the year is considered week 1. For example, in the year 2016, the first Thursday is January 7, and that is why week 1 begins on 4-Jan-2016.
More info: https://en.wikipedia.org/wiki/ISO_week_date
