On my team, we have to constantly manually change week numbers. Since its a report done once a week where we have to use the same template. i wanted to figure out a way wheer we just change 1 cel to this current week and then a formula changes all the rest to the next week in a series of weeks. I am not allowed to always have a weeknumber() formula in the first week cell remain as a formula bc it would chance all teh data if we were to open the file at a later date.
So i did some reasearch and forund this site: https://techcommunity.microsoft.com/discussions/excelgeneral/convert-week-of-the-year-into-a-date/3571944
and i came up with a bit of a long formula that works. I would liek to know if there is a simpler way to have these.
example: A1: 202448
the formula i have come up with to convert the week number to a date + 7 days & then convert back is:
=TEXT((DATE(LEFT(A1,4),1,-2)-WEEKDAY(DATE(LEFT(A1,4),1,1))+RIGHT(A1,2)*7)+7,”YYYY”)&TEXT(WEEKNUM((DATE(LEFT(A1,4),1,-2)-WEEKDAY(DATE(LEFT(A1,4),1,1))+RIGHT(A1,2)*7)+7,1),”00″)
results in 202449
Is there as simpler way to do this?
