WEEKNUM - start on custom date

discoadz

New Member
Joined
Mar 25, 2020
Messages
2
Platform
  1. MacOS
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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,354
Office Version
  1. 365
Platform
  1. Windows
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
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,354
Office Version
  1. 365
Platform
  1. Windows
Splendid. You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,530
Messages
5,625,357
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top