cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mguerra
Advocate I
Advocate I

Convert date to week -Year starts week of Jan 1st

I have been struggling for a few days now trying to adapt the solution in this post Convert Date to Week Number (number format) (microsoft.com) to fit my use case. My company starts the year the week of January 1st, meaning that what the rest of the world seems to have as week 53 of 2020, for us is week 01 of 2021. Truly appreciate if someone can please help me with this. 

2 REPLIES 2
rebeccas
Super User
Super User

What do you need to do with the week number? I am just trying to understand where you need to see it and how it will need to be used and updated. 

 

This gives you the current week number:

RoundDown(
(
RoundUp(
DaveValue_DateSelected.SelectedDate-Date(Year(DaveValue_DateSelected.SelectedDate-Weekday(DaveValue_DateSelected.SelectedDate-1)+4),1,3),0)+
Weekday(Date(Year(DaveValue_DateSelected.SelectedDate-Weekday(DaveValue_DateSelected.SelectedDate-1)+4),1,3))+5)
/7,0)

 

And if it is just for a label you could just subtract 1 from it or something like that but it really depends on what you you are doing with it.

Thank you for your response. Substracting 1 will not necessarily solve the problem as it gives me week 52 of 2020 instead of week 01 of 2021. 

As for the use case, it's a display of a selected date as in the picture attached -pls keep in mind that the picture has the wrong week for my use case (displays 20WW53 when it should be 21WW01). 

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,014)