Hello,
Could you please give me advice to calculate the number of weeks (Duration) between 2 weeks? My problem is that the format of week is WW.YYYY and the formula should consider the year as well.
Thank you for any support you can offer.
Here is an example with the desired results (I hope this is possible somehow):
Start End Duration
06.2020 08.2020 3 weeks
51.2020 02.2021 5 weeks
your two suggested answers are at odds with each other. The answers should be 3 and 4 respectively.
=(RIGHT(B3,4)-RIGHT(A3,4))*52+VALUE(LEFT(B3,2))-VALUE(LEFT(A3,2))+1
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi AliGW,
Thank you very much for your quick answer. I have an issue with your formula regarding the second case.
Week 51.2020 starts on 14.12.2020 and week 02.2021 ends on 17.01.2021, we have 35 days between 14.12.2020 and 17.01.2020, so the duration is 5 weeks.
OK - so it can't be done without proper dates, then, and the data sample you have provided is inadequate.
Thank you the help AliGW, you solved the 50% of my problem.
I wait if somebody can offer other formula for the second case.
FYI the ww.yyyy text formatting does not exist - ww is not recognised. You can use d, m and y for date formatting.
As I said, you need proper dates to be able to do this. Excel cannot determine from text when in the week it is meant to be - it is only text, not a date value.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks