0
I would use this formula in the Week Duration column:
="Week " & TEXT(ROUNDDOWN((DAYS(C3,$D$8)/7)+1,0),"00") & " - " & TEXT(ROUNDDOWN((DAYS(D3,$D$8)/7)+1,0),"00")
The initial Start date needs to be in it's own cell though (so you can edit it easily if needed)
See Screenshot:
I've changed the dates to 2019 so that the initial start week starts on a monday (wondering if this was a typo in the OP)
Also changed the End date on Task 4 to show that it works across years
NB: This calculates the weeks starting on the same day of the week as the Initial Start Week value, so it that is a Wednesday, the following Tuesday will still be week 1, and only the next Wednesday will be the start of Week 2
Great works great for me. thank you. – Loizos Vasileiou – 2019-10-23T15:52:44.543
0
There is a function within Excel that you can use to return the week number, WEEKNUM
Depending on your locality, you might want to change how the week is calculated. By default, it uses Sunday as the start of the week. You can use =WEEKNUM(A1,2)
to tell Excel to use Monday as the first day of the week (3 is Tuesday, 4 is Wednesday etc.).
You could also use the ISOWEEKNUM function which takes a date and returns a week number (1-54) that follows ISO standards, where weeks begin on Monday and week number 1 is assigned to the first week in a year that contains a Thursday.
When you have worked out the week number as it sits in the year, you can take the initial week value from the task week value. i.e. if the intial start week was 23 and the task started on week 25, then it would be task week 2 (25-23)
Using this, you can then build a function to CONCATENATE
the week numbers and the texts into the format that you want i.e. =CONCATENATE("Week ",A1,"-", A2)
Would this work in an academic environment where weeks run from September to September ? (ie 1st week in January although a subsequent week to the last week of December the previous year, would be a lesser value - 01 - 52 = -51
) – Smock – 2019-10-23T15:32:09.750
Did you mean to use 2018 or 2019 dates? 14/10/2018 is a Tuesday, 14/10/2019 is a Monday – Smock – 2019-10-23T15:39:29.150