Number weeks from a given date

[ollyhughes1982]

Hi all,

I have a worksheet (link below) in which I record my (not)parkruns. The (not)parkrun initiative has been running since 18/06/2020, so the first week that it was possible to record one was 15/06/2020 - 21/06/2020 (Week 1). In my worksheet, I want to be able to identify what possible week number each run has been completed in (column BD). I have manually populated column BD for now, but need a formula to calculate this for me.

Up until week 141 I did at least 1 run in every possible week, but in week 142 I didn’t record any, therefore this is where columns AK and BD should diverge. i.e. The number of possible weeks becomes 1 more than the cumulative number in which I have completed runs. The second time I miss a run the gap will become 2 and the next 3 and so on…

So, I need a formula to calculate column BD (possible weeks) - which I have currently manually populated in yellow. i.e. make 15/06/2020 - 21/06/2020 the first Monday - Sunday (week ‘1’) and from then using the date in column B populate column BD with the relevant possible week number.

Link to file (OneDrive): BD Column Calculation.xlsx

Thanks in advance!

Olly.

 

[Fluff]

How about

=NETWORKDAYS.INTL(DATE(2020,6,15),B4,"0111111")-1

 

[ollyhughes1982]

Thank you, I had never seen that function before. It worked with one small tweak of the start date: =NETWORKDAYS.INTL(DATE(2020,6,7),B4,"0111111")-1 instead of =NETWORKDAYS.INTL(DATE(2020,6,15),B4,"0111111")-1, which then makes the first week week 1, rather than 0.

Thanks so much.

 

[Fluff]

It should actually be

=NETWORKDAYS.INTL(DATE(2020,6,15),B4,"0111111")

Your calculations go wrong on row 120. You have that as 56 when it should be 57