WEEKNUM - start on custom date

[discoadz]

Hi all,

I'm trying to use the week number function but I need to change the start date from 1st Jan 2020 to 28th April 2020. This is a Tuesday and I need each week to start on a Tuesday.

So for example 28 April 2020 - 4 May 2020 is Week 1
5 May 2020 - 11 May 2020 is Week 2 and so on

Is anyone able to help with a custom formula to set Week 1 from 28th April?

Many thanks

 

[DRSteele]

Welcome to the forum.

Unless I'm missing something, this should be it. Notice that 43949 is the Excel Date Serial Number for April 28, 2020.

=WEEKNUM(A1,12)-WEEKNUM(43949,12)+1

or else just hard-code the adjuster.

=WEEKNUM(A1,12)-17

 

[Phuoc]

Or try:

=INT((A1-"2020/4/28")/7)+1

 

[discoadz]

awesome - thank you guys - works a treat

 

[DRSteele]

Splendid. You're welcome.