How to convert start/end date to week duration in Excel?

Question

I was wondiring how to convert Start Date and End Date to week duration assuming that the results are always prime numbers?

For example:

Answer 1

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

Answer 2

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)