Log In
Register
Home
New Member
August 29, 2019
Offline
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?
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
Answers Post
New Member
August 29, 2019
Offline
That seems to have worked. Thank you!
Would you mind explaining what it's doing, so I can understand it better?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
It will take me a week to write about this 🙂
You will have to read a few articles: https://stackoverflow.com/ques.....e-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.
1 Guest(s)
